Solved

import .csv data into sql server 2008

Posted on 2014-04-07
5
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

624 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