Solved

import .csv data into sql server 2008

Posted on 2014-04-07
5
249 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
ID: 39985773
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
ID: 39987653
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
ID: 39987703
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
ID: 39993231
I will respond by tomorrow during biz hours. Thanks.
0
 

Author Comment

by:pae2
ID: 40013809
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore 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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

827 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