Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problems with Bulk Insert format into SQL Server from CSV file

Posted on 2008-06-17
5
Medium Priority
?
221 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

609 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