Importing from CSV: Access Addin or Third-Party Software?

[Eric Tubbs]

Greetings,

I would like to inquire if there is some third party software or add-in that
would possibly handle many different types of imports. I have a CSV file
with 70 columns of data that needs to be imported into the database and then
the data needs to be processed into the corresponding tables.

I have read many posts in the newsgroups about importing text/csv files and
just about all of the situations suggested that a VBA solution would be
needed.

Would the DTS (Data Transformation Services) that is part of SQL Server be
able to handle these types of scenarios?

I have come across the "Import Wizard" from Beside.com that will be able to
handle all of my text conversion issues that Access is not able to handle
with the Text Import Wixard.

While searching for numerous programs and other utlities, I came across a
piece of software called "DeZign for Databases" from Datanamic,
http://www.datanamic.com/, any thoughts about this software program for data
modeling?

I have not finished the database design yet, but it should be done within
the next two weeks, as I am still learning the ropes of database design.
Hopefully by that time, I will be able to supply you with more details.

Is there a solution out there beside writting code?

Thanks,

Eric

 

[Mike Painter]

Greetings,

I would like to inquire if there is some third party software or add-in that
would possibly handle many different types of imports. I have a CSV file
with 70 columns of data that needs to be imported into the database and then
the data needs to be processed into the corresponding tables.

I have read many posts in the newsgroups about importing text/csv files and
just about all of the situations suggested that a VBA solution would be
needed.

Would the DTS (Data Transformation Services) that is part of SQL Server be
able to handle these types of scenarios?

I have come across the "Import Wizard" from Beside.com that will be able to
handle all of my text conversion issues that Access is not able to handle
with the Text Import Wixard.

While searching for numerous programs and other utlities, I came across a
piece of software called "DeZign for Databases" from Datanamic,
http://www.datanamic.com/, any thoughts about this software program for data
modeling?

I have not finished the database design yet, but it should be done within
the next two weeks, as I am still learning the ropes of database design.
Hopefully by that time, I will be able to supply you with more details.

Is there a solution out there beside writting code?

Probably not if they are "real" columns.
Chances are that the code required will not be that much and much of it
probably can be done by running a set of queries.

You might want to look at
http://www.datawatch.com/dataconversionsoftware/data_pump.htm

 

[John Nurick]

Hi Eric,

Greetings,

I would like to inquire if there is some third party software or add-in that
would possibly handle many different types of imports. I have a CSV file
with 70 columns of data that needs to be imported into the database and then
the data needs to be processed into the corresponding tables.

I have read many posts in the newsgroups about importing text/csv files and
just about all of the situations suggested that a VBA solution would be
needed.

If it's a well-structured CSV file with fewer than 255 fields, Access's
Text Import Wizard should be able to import it and you probably won't
need VBA. "Well-structured" includes:
-every record consists of exactly the same fields in the same order;
-any field whose data contains a comma or a linebreak is "delimited" or
"qualified" with quotation marks;
-any quotation mark within a quoted field is doubled "".

Once you've got the data into an Access table it's then just a matter of
creating and running a series of queries to gather data from this and
append the appropriate records to the tables in your database structure.
Possibly the Access table analyzer wizard would be useful; you might be
able to use the queries it creates as a basis for some of your own.

Would the DTS (Data Transformation Services) that is part of SQL Server be
able to handle these types of scenarios?

DTS can import some things that Access can't; but it wouldn't be an
advantage here unless you're already familiar with it and with SQL
Server.

I have come across the "Import Wizard" from Beside.com that will be able to
handle all of my text conversion issues that Access is not able to handle
with the Text Import Wixard.

This product is designed for importing data from files with more complex
and variable structures. If you've got a well-structured CSV file you
won't need it.