Solved

Problems with Bulk Insert format into SQL Server from CSV file

Posted on 2008-06-17
5
213 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
  • 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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…

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now