Importing Data from .csv file

[Lee-Anne Waters via AccessMonster.com]

Hi all,

i'm hoping someone can help me with this as i am really at a loss.

what i have is a large csv file that i import directly into a database.
one of the cells in the file contains a lot of formatted data that looks
like this

F 0297470043, N
W HOBL,BUWN80,S12RSU,10#,42 N
P AM31,C8,VUT
X BURD,E4003,1,X,756 Y
T BURD,E4003,M,756,ADSL48,2,6,36
T BURD,E4003,S,756,ADSL48,2,6,36
X BURD,E4003,1,C,756
M BURD01,4,649 Y
D BURD,P78,O,102 N

when i import it it looks like this

F
0297470043, NW
HOBL,BUWN80,S12RSU,10#,42 NP AM31,C8,VUT
X BURD,E4003,1,X,756 YT
BURD,E4003,M,756,ADSL48,2,6,36 T
BURD,E4003,S,756,ADSL48,2,6,36 X BURD,E4003,1,C,756
M BURD01,4,649 YD
BURD,P78,O,102 N

is there any way i can reformat this in a better way when i have to send
the information from the system.

thanks

Lee-Anne

 

[tina]

did you create an import specification to customize and control the import,
Lee-Anne?

 

[Lee-Anne Waters via AccessMonster.com]

hi Tina,

this is code line i use to import to a temp table

DoCmd.TransferText acImportDelim, , "ImportData", Forms!frmImport_Alloc!
FiletoOpen, True

i'm not sure how the specification names work so i will check them out. if
you know of anywhere else where this information may be available then
please let me know

thanks
Lee-Anne

 

[tina]

run the Import Wizard (File | Get External Data | Import) manually the first
time - to manually create the Import Specification and the "receiving"
table. go through each step of the import process in the wizard, setting
field widths, data types, field names, etc. then click on the Advanced
button, set any other parameters such as Date, Times, Numbers handling, then
click Save As. the system will provide a default Specification name, which
you can change if you want, then save the specification you just created so
painstakingly.

once you've created the import specification, you can run your import via
code just as you're doing now, simply including your Specification name in
the arguments, as

DoCmd.TransferText acImportDelim, "MySpecificationName" , "ImportData",
Forms!frmImport_Alloc!FiletoOpen, True

try checking Access Help for info on using the Import Wizard. though if i
recall correctly, i learned how to use the wizard effectively through a lot
of trial-and-error...just playing with the wizard over and over until i
understood how to manipulate it and how each option affects the import. if
you're not comfortable doing this in your "working model" database, just
create a new db and play with the wizard there. once you've created the
Import Specification you need, and the destination table you need, you can
open your "real" db and import the specification (and the table), no
problem.

hth

 

[Lee-Anne Waters via AccessMonster.com]

thanks very much

you have been a great help....

 

[tina]

you're very welcome
once you try out import specifications, i think you'll love them. they make
it so so so much easier to control the import of text type files, over Excel
files.