Import csv and split data

[kuipers78]

hello,

I want to import a .csv file into Access 2003 using VBA-code. However, the data is being stored in the first column, separated by the ':' mark. Every record is one row. So, only column A has been filled with data in the .csv file.

My 2 questions:

Is there an Access function for importing a .csv file? I know the function for importing a .xls file is called 'TransferSpreadsheet', but that doesn't seem to work for the .csv extension...

Secondly, is it possible to split the data into different table fields while importing in Access? The ':' mark should be the trigger for a new field.
Or should I do this in the .csv file before importing?

For example, in Excel:

Column A
:William :Weaver :12-01-1976
:Maria :Manson :01-09-1980

Should be imported into an Access table like this:

Field1 Field2 Field3
William Weaver 12-01-1976
Maria Manson 01-09-1980

Any help would be greatly appreciated! Thanx!

 

[gemma-the-husky]

in access you would have to import the file as is, THEN write a routine to generate another table form the import, which would split the columns. you would have to search for the colons to split them in a function.

I tthink there is an inbuilt split function, but its not hard to write a function to do it anyway.

 

[kuipers78]

Thanks for your quick reply!
You are right, I can split the data into a new table using code, but the question remains: is it possible to split it WHILE importing? And secondly, how do I import a .csv file into Access?

 

[Ron_dK]

Thanks for your quick reply!
You are right, I can split the data into a new table using code, but the question remains: is it possible to split it WHILE importing? And secondly, how do I import a .csv file into Access?

To import a .csv , search for Transfertext

While writing your import specification, you might use the [:] to seperate text for various fields.

Hth

 

[kuipers78]

That's right! I just figured it out myself. It works the same as importing a .txt file, so I now know what to do.

Thanks for all help.

 

[gemma-the-husky]

can you clarify this - were you able to split the import line while doing the import?

ignore this - i've got it now - i tend to use A97, and this feature isn't in A97.