469,890 Members | 1,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

Split Column, Combine Two rows

Help please

i have a file which im importing, however it is a single column, the
data looks like

C8517673505
N7062175
C8517673516
N7062178
C8517673527
N7062174
C8517673538
N7062187
C8517673549
N7062188

i want to basically put these records into another table with two
columns, to look like this

C8517673505 N7062175
C8517673516 N7062178
C8517673527 N7062174
C8517673538 N7062187
C8517673549 N7062188

Any suggestions on how to accomplish this?.

thanks
Nov 12 '05 #1
3 11382
Create two queries. In the first query, set the criteria as "C" & "*" and in the
second query set the criteria as "N" & "*". Export each query into a different
Excel worksheet. Copy and paste the "N" numbers in the column beside the "C"
numbers. Go back to Access, open the database windows to Tables, click on File -
Get External Data and follow the instructions to import the two columns into a
new Access table.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Stephen Matthews" <sm*******@burbidge.co.uk> wrote in message
news:d5**************************@posting.google.c om...
Help please

i have a file which im importing, however it is a single column, the
data looks like

C8517673505
N7062175
C8517673516
N7062178
C8517673527
N7062174
C8517673538
N7062187
C8517673549
N7062188

i want to basically put these records into another table with two
columns, to look like this

C8517673505 N7062175
C8517673516 N7062178
C8517673527 N7062174
C8517673538 N7062187
C8517673549 N7062188

Any suggestions on how to accomplish this?.

thanks

Nov 12 '05 #2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Another way would be to write a VBA routine that would read each 2
line pair and put the results into the production table.

== air code ==

const SQL_IN = "SELECT * FROM ImportTable"

dim db as dao.database
dim rsIn as dao.recordset
dim rsOut as dao.recordset
dim strCol1 as string, strCol2 as string

set db = currentdb
set rsIn = db.OpenRecordset(SQL_IN)
set rsOut = db.OpenRecordset("FinalTable")

do while not rsIn.EOF

' Get the data in 2-line pairs from the
' imported file.
strCol1 = rsIn(0)
rsIn.MoveNext
strCol2 = rsIn(0)

' Put the 2-line pair into 1 record
rsOut.AddNew
rsOut!Column1 = strCol1
rsOut!Column2 = strCol2
rsOut.Update

rsIn.MoveNext

loop

== end air code ==

You'd have to change the table and column names to your usage. You'd
also have to add error handling routines - watch out for EOF when not
expected (read the 1st line of a 2-line pair and trying to read the
2nd line, but it's not there).

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7Wf54echKqOuFEgEQKwqQCgry2coautzmBtovYTg9Wxp8 o7MhYAn1hk
6J4xERdXfdYvdfilDrFC42Z5
=osEX
-----END PGP SIGNATURE-----
PC Datasheet wrote:
Create two queries. In the first query, set the criteria as "C" & "*" and in the
second query set the criteria as "N" & "*". Export each query into a different
Excel worksheet. Copy and paste the "N" numbers in the column beside the "C"
numbers. Go back to Access, open the database windows to Tables, click on File -
Get External Data and follow the instructions to import the two columns into a
new Access table.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Stephen Matthews" <sm*******@burbidge.co.uk> wrote in message
news:d5**************************@posting.google.c om...
Help please

i have a file which im importing, however it is a single column, the
data looks like

C8517673505
N7062175
C8517673516
N7062178
C8517673527
N7062174
C8517673538
N7062187
C8517673549
N7062188

i want to basically put these records into another table with two
columns, to look like this

C8517673505 N7062175
C8517673516 N7062178
C8517673527 N7062174
C8517673538 N7062187
C8517673549 N7062188

Any suggestions on how to accomplish this?.

thanks


Nov 12 '05 #3
"Stephen Matthews" wrote
i have a file which im importing,
however it is a single column, the
data looks like

C8517673505
N7062175
C8517673516
N7062178 ... i want to basically put these records into
another table with two columns, to look
like this

C8517673505 N7062175
C8517673516 N7062178


Are you, in fact, importing a _text file_ or are you importing a _table_?
The answer will, of course, differ for each of these.

It's a little confusing to me because you say you are importing a "file" but
then that you want to put the data into "another table".

Larry Linson
Microsoft Access MVP
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Antanas | last post: by
1 post views Thread by rcamarda | last post: by
1 post views Thread by Oonz | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.