?
Solved

Problems with Bulk Insert format into SQL Server from CSV file

Posted on 2008-06-17
5
Medium Priority
?
220 Views
Last Modified: 2013-11-26
I am importing data from a CSV file into a SQL Server database using the Bulk Insert method however I am running into a formatting problem. One of the columns I am importing has ten-digit ID number, many of which begin with one or more zeroes. The zeroes are important to the ID number and need to stay in place. However when I import them to SQL Server they zeroes always get deleted. I have tried opening the CSV in Excel and formatting the cells using Custom formatting and "0000000000", however even after saving the file and confirming it re-opens with the zeroes in place it still does not work for the transfer to SQL Server. Any suggestions?
0
Comment
Question by:bpfsr
[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 53

Expert Comment

by:Dhaest
ID: 21802971
Can you try the following: in excel, select the column and make the type equal to text.
In what kind of column is the data stored in sql-server ?
0
 

Author Comment

by:bpfsr
ID: 21803078
When I make the type text in Excel it still drops the zeroes, I am using varchar in SQL Server
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21803160
Can you show your connectionstring to excel
0
 

Author Comment

by:bpfsr
ID: 21803450
use bookit
create table BookitTest
(
SKU varchar (15),
ISBN varchar (10),
Title varchar (150),
Condition varchar (25)
)
BULK INSERT BookitTest
    FROM 'C:\Documents and Settings\Brian Fisher\My Documents\Softbyte Files\FileExchange_Response_3729638.csv'
    WITH
    (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'
    )
select * from bookittest

0
 
LVL 53

Accepted Solution

by:
Dhaest earned 2000 total points
ID: 21819788
Perhaps the easiest way is to import it throught the bulk insert.
When the bulk insert has been done, run a query to format the column with all the leading 0
SELECT REPLICATE( '0', 10 - LEN(@a))+@a
this will add a max of 10 leading zeroes.
here @a is varchar
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 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