Solved

Problems with Bulk Insert format into SQL Server from CSV file

Posted on 2008-06-17
5
212 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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…
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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

939 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

9 Experts available now in Live!

Get 1:1 Help Now