Solved

import .csv data into sql server 2008

Posted on 2014-04-07
5
251 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

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 article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

740 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