Converting a CSV file to MSAccess table

[nikko]

hi y'all!

i have a very large CSV file that i need to convert to an MSAccess table.

anybody got ideas...?

more power to you all!

[boumerlin]

You can use ADO.Net with Excel and with CSV. You'll have to dig a little deeper to find how to use ADO.Net with a CSV, but below is a link on Excel:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

Is this a one-off operation? If it is, I would just open Access and import the data directly.........you'll save yourself a lot of time!

If this is a data import application you will need to run routinely, I would push for some format other than CSV or Excel, or otherwise access your data from some low-level file access operation. I've been shot in the foot one too many times with CSV/Excel and ADO for data import. XML is much better for this sort of thing, if you can get it!

Linda

[HanneSThEGreaT]

I found this on PlanetSourceCode (http://www.planetsourcecode.com)

[nikko]

thanks to you both for your replies...:)

linda:

the conversion would be a routine operaton.my boss forwards a CSV file to me for processing from time to time.i never asked him why its in that format, but i assume it's what's most comfortable for him.converting it to XML is a great idea, though...help on that one...? :D


hanne:

thanks, bro...you have proved to be a most useful creature...
i'll tinker with that conversion code for a while, and we'll see how that goes..


more power to you both!

[nikko]

hanne:

i modified the code a bit, then tried it.it works fine for a CSV file that has a maximum of 85045 records.anything above that and it generates an error like so:

"input array is longer than the number of columns in this table"

my CSV file has 120,000 records, give or take...:((

[boumerlin]

Hi Nikko,

I see you've found one of the first gotchas in working with Excel/CSV and ADO. Like I said before, I've found it can be a long, frustrating road and have actually used the File System Object instead of ADO with CSV. I think the FSO is still around in VB.Net.

You asked about getting your data in XML. There is a utility in Excel XP and beyond to generate XML from a spreadsheet. I've heard the results can be disappointing in the way of incomprehensible tags and getting XML documents that are not "well formed," so keep that in the back of your mind.

Sorry I usually generate my XML right out of the SQL Server database so I don't know how much help I'll be, but I did Google "Excel to XML" and found a couple of good leads. You might want to look into these (the first two look very promising):

http://www.meadinkent.co.uk/xl_xml1.htm
http://www.codeproject.com/useritems/xls2xml.asp
http://www-128.ibm.com/developerworks/xml/library/x-tipexc.html

I hope that helps!

Linda

[nikko]

thanks again, Linda, but i found a way to convert a large CSV file into a dataset...now i am thinking of doing away with the Access table completely, and use an excel file instead...

at the moment, i already know how to query a sheet, and some particular columns (except if i wish to query columns A and D only, for example).

if i can do that, life would be a lot easier for me...

more power to you!

[boumerlin]

Glad you found a solution. I'm just curious......what are you using to convert the CSV to dataset?

[nikko]

hi...tried to email you...i believe we have so much to learn from each other...but anyways, just to satisfy your curiousity.... :D

here's the project i've been using for practice.if u have time, please improve the code, then send it back...?

tnx!

[boumerlin]

Thnx Nikko!

I downloaded the file, but haven't had a chance to look at it yet. I've been chasing my tail with Crystal today!

Linda

[nikko]

well...i successfully converted the CSV file into a virtual datatable, but i cant seem to save the contents into an actual Access table...