Solved

import .csv data into sql server 2008

Posted on 2014-04-07
5
241 Views
Last Modified: 2015-04-05
I need to import a 5GB .csv file into SQL Server 2008 R2SP2. I tried using the import/export wizard without success. I selected the following when running the wizard:

01. flat file (not sure if this is right)
02. code page: 1252; ANSI - Latin I; (not sure if this is right)
03. format: delimited (the data are just in columns within a .csv; not sure if this is right)
04. text qualifier: none
05. header row delimiter
06. header rows to skip: 0
07. row delimiter: default is {LF}; there is no delimiter, but i'm forced to choose something; went with default LF
08. column delimiter: comma {,}; there is no delimiter, but i'm forced to choose something; went with default LF

The wizard did create the columns successfully, but it put double-quotes around the column-names. No data was imported. How can I import data stored in a .csv file that is nothing abnormal other than size? It has a header file (column names) with a large quantity of data. I don't care what mechanism I use. I just thought the import/export wizard would be easiest.

Thanks,

pae2
0
Comment
Question by:pae2
  • 3
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
07. row delimiter: default is {LF}; there is no delimiter, but i'm forced to choose something; went with default LF
There has to be a row delimiter.  Perhaps it is CrLf.  If you are not sure then look at the data with a hex editor.

08. column delimiter: comma {,}; there is no delimiter, but i'm forced to choose something; went with default LF
Again if this is a CSV, there has to be a column delimiter.  Perhaps it is tab.  

Are you sure it is a CSV and not fixed width.  Try posting a sample here.
0
 

Author Comment

by:pae2
Comment Utility
Anthony, thanks for the help! I will post a screenshot at some point tomorrow. Anyway, the file icon looks like Excel, but it's not an Excel document. The file extension is .csv.

Thanks!

pae2
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
Comment Utility
Anyway, the file icon looks like Excel, but it's not an Excel document.
That is because Excel "understands" csv files.  At least files with an extension of csv.  Of course that does not mean it is a true csv file.  You can rename a file to have any extension.

Rather than posting a screen shot, I would post a sample of your data.  We can then see what type of delimiters it actually has.
0
 

Author Comment

by:pae2
Comment Utility
I will respond by tomorrow during biz hours. Thanks.
0
 

Author Comment

by:pae2
Comment Utility
Apologies, I will get back to this. I had other production priorities. I will aim to get to this tomorrow during biz hours. pae2
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now