Clicky

The following query
SELECT *
FROM OPENROWSET(
  'MSDASQL',
  'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=h:\;Extensions=CSV;',
  'SELECT * FROM h:\nslookup.csv')

Against this file

Name,A,IP
001-000039,A,172.16.221.21
001-000040,A,172.16.222.11
001-000061,A,172.16.222.47

returns this result

Name      A      IP
001-000039      A      172.1622
001-000040      A      172.1622
001-000061      A      172.1622

It appears that the IP column is treated as a number.  How can I get the IP?

asked 12/12/2011 05:08

Kalvyn's gravatar image

Kalvyn ♦♦


5 Answers:
Open the CSV on excel and set the format of the column as Text and be sure that the table where you're importing (the SQL Table) is text and not number
link

answered

k-designers's gravatar image

k-designers

I am working with a text csv file, not excel.  I am trying to automate the process so using excel change the format of a column is not an option.  Isn't there a way with OPENROWSET to define column formats?
link

answered 2011-12-12 at 13:18:23

Kalvyn's gravatar image

Kalvyn

I got the results I expected using

SELECT * FROM OPENROWSET( BULK 'h:\nslookup.csv', FORMATFILE = 'h:\format.xml')

where format.xml is

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="15" />
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="A" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="IP" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>
link

answered 2011-12-12 at 13:33:01

Kalvyn's gravatar image

Kalvyn

My own solution.

link

answered 2011-12-12 at 14:12:10

Kalvyn's gravatar image

Kalvyn

Trial and error and Google helped me find the answer.
link

answered 2011-12-12 at 14:12:44

Kalvyn's gravatar image

Kalvyn

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×11
×132

Asked: 12/12/2011 05:08

Seen: 320 times

Last updated: 12/16/2011 05:20