Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-29-2015, 11:14 AM   #1
jpaokx
Newly Registered User
 
Join Date: Sep 2013
Posts: 37
Thanks: 6
Thanked 2 Times in 2 Posts
jpaokx is on a distinguished road
Import problem from csv file

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!

jpaokx is offline   Reply With Quote
Old 09-29-2015, 11:34 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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?
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 09-29-2015, 02:12 PM   #3
jpaokx
Newly Registered User
 
Join Date: Sep 2013
Posts: 37
Thanks: 6
Thanked 2 Times in 2 Posts
jpaokx is on a distinguished road
Re: Import problem from csv file

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?

jpaokx is offline   Reply With Quote
Old 09-30-2015, 06:36 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Import problem from csv file

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;DAT ABASE=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.

__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 09-30-2015 at 06:45 AM.
arnelgp is offline   Reply With Quote
Reply

Tags
commas , csv , import

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import file Problem Haynesey Tables 3 01-18-2007 05:53 AM
Import Text File - Problem wazza General 1 07-01-2004 03:04 AM
import csv file problem thebolly Tables 3 04-07-2003 06:14 PM
*.csv file import problem Danielf Modules & VBA 2 04-22-2002 05:03 AM
Import .csv file problem Louise Modules & VBA 3 08-24-2000 02:25 PM




All times are GMT -8. The time now is 12:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World