×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

CSV file to SQL SERVER

CSV file to SQL SERVER

CSV file to SQL SERVER

(OP)
I need to convert a CSV File (beyond the normal problem of the comas, each two rows need to become 1 row in SQL Server (6.5)). Any help would be great.

RE: CSV file to SQL SERVER

Can you use VB? Have you used BCP before? The bcp.fmt file tells bcp what the format of the file is.

RE: CSV file to SQL SERVER

(OP)
The method that I was going to use, is to import the CSV to an Access table (with commas and such left in). Then use VB to parse out the information (using the instr) to break at the commas, and populate the text boxes. Then... call out to SQL Srv, and enter the data to it. In theory its sound, but I wanted to see if there was an easier and less time consuming way to do this.
As to the BCP, it doesn't ring a bell with me. If its an easier method, I'm all for it.
Thanks

RE: CSV file to SQL SERVER

BCP is the way to go. It's a command line utility but if I'm not mistaken there is an object front-end that can be manipulated in VB/VBA.

RE: CSV file to SQL SERVER

Have you tried using the Data Transformation Services? Or isn't that available in 6.5?

RE: CSV file to SQL SERVER

DTS is new to SS7

RE: CSV file to SQL SERVER

Yes I would recommend BCP. However, as an alternative I have used the following in the past, wrapped up in a Transact SQL stored procedure:

1. Create a (temporary) table to hold the file. eg.

create table #input_file ( line varchar(255) NULL)

2. Use an extended procedure to run a command shell, issuing a 'type' command on the file:

insert into #input_file (line)
exec master..xp_cmdshell 'type c:\filename.csv'

This loads the output of the command shell (ie. the file contents) into your table.

3. Now write code to do substring manipulation to extract the columns from your csv data eg.

update target_table
from #input_file
set target_table.time_stamp = convert(datetime,substring(#input_file.line, 1, 15))
...
etc etc

4. Finally, if you've created a temporary table remember to clear it down and blow it away.

One adantage of this method over BCP is that it fires any triggers that you've defined.

Your DBA may complain about security - the 'xp_cmdshell' runs under the A/C that starts the DB, however they can create a separate NT A/C just to run xp_cmdshell, should they feel strongly.


Hope that this helps.

Incidentally my original use for this was to execute a 'dir /s' command in the shell to recursively build up a file tree and store file details in a DB table.

Cheers,

Martin.Ramsay@company-net.co.uk

RE: CSV file to SQL SERVER

Should have added to my original post ...

To manipulate 2 rows at a time as you asked in your original request then you could process the table using a cursor, extracting the data into @variables and then processing the results from the 2 previous rows read in to the cursor using TSQL to do the concatenation.

Cheers,

Martin

Martin Ramsay
Martin.Ramsay@company-net.co.uk
My employer
Looking for interesting work in Canada - any offers ?

RE: CSV file to SQL SERVER

Martin,
Excellent tip. I hadn't realised that the results from an xp_cmdshell could be inserted into a table. + as a cunning way of importing data it could be invaluable.

Jez.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close