Import problem from csv file (1 Viewer)

jpaokx

Registered User.
Local time
Today, 03:07
Joined
Sep 23, 2013
Messages
37
Hello,

I have a problem importing a file with around 3 million records.
I get a csv comma delimited file with no quotations from an external source.
It contains consumer data with demographics and personal details. However, the address field contains commas (but not all records).
This means that if I try to import the file, then I'll get a lot of import errors because of this.

For example, the file contains:
id,date_of_purchase, date_of_drop_off,name, address, postcode
1, 01/01/2005 00:00:00, 03/01/2007 00:00:00, John Thomson, 342/3, London Street, Birmingham, B32 1FG.

In this case, the data look fine if I open the file in Excel (but, of course, not all of them will appear as it doesn't accept more than 1million records). But, if I import them into Access, then the second part of the address will move incorrectly into the postcode field.

Any ideas how to import it successfully but keep the commas in the address field?


Thanks!
 

MarkK

bit cruncher
Local time
Today, 03:07
Joined
Mar 17, 2004
Messages
8,178
There are many possibilities here. Pre-process the file, so write code that removes commas from each line after the fourth comma. Then your data is correctly delimited--and add the commas back into the address after it's in a table. Or replace the first four commas with some other delimiter and then read in that file. Or, with your code that reads each line, store the records into the table straight from that loop, so grab the first four fields one at a time, then whatever is left is the address.

I would say the ways you can do this are only limited by your programming skill and imagination. How is your programming?
 

jpaokx

Registered User.
Local time
Today, 03:07
Joined
Sep 23, 2013
Messages
37
Hi...

Thanks for replying so quickly.

I am afraid that I lack of programming skills. From what I can see, all your suggestions require that.
Is there any link that suggests this solution and, so, I can replicate to my case? Alternatively, is there any other (more straightforward) way for someone with no programming skills?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:07
Joined
May 7, 2009
Messages
19,169
i have tested with comma data and it import just fine.

DBEngine(0)(0).Execute ("Insert Into tblHoliday " & _
"([id], [date_of_purchase], [date_of_drop_off], [name], [address], [postcode]) " & _
"select [id], [date_of_purchase], [date_of_drop_off], [name], [address], [postcode] " & _
"from [Text;FMT=Delimited(,);HDR=Yes;IMEX=2;ACCDB=YES;DATABASE=Z:\].[Holiday#csv]")

just replace tblHoliday with your ms access table to update and Holiday (on Holiday#csv) with your csv file, and Z:\ with the right path of your csv.
 
Last edited:

Users who are viewing this thread

Top Bottom