Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Problems with Bulk Insert format into SQL Server from CSV file

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
bpfsr
Asked:
bpfsr
  • 3
  • 2
1 Solution
 
DhaestCommented:
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
 
bpfsrAuthor Commented:
When I make the type text in Excel it still drops the zeroes, I am using varchar in SQL Server
0
 
DhaestCommented:
Can you show your connectionstring to excel
0
 
bpfsrAuthor Commented:
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
 
DhaestCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now