Text file as a data source...

Question

Post reply

Text file as a data source...

Christopher Klein

SSCrazy

Points: 2648
More actions
November 30, 2005 at 1:06 pm

#95963

Ok, so I have an interesting problem (at least a coding one). I know you can use an external document as a data source ala;
SELECT
cast(EX.intMasteracctID as int) as intMasterAcctID,
cast(EX.MBL as varchar(10)) as MBL,
cast(EX.intID as int) as intBuildingID,
cast(EX.[Year Built] as int) as intYearBuilt,
cast(EX.[Eff Year] as int) as intEffectiveYear,
cast(EX.Condition as int) as intConditionCode,
cast(EX.Grading as int) as intGrading
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
        'Data Source=C:\buildings.xls;Extended Properties=Excel 8.0')...[TEST$] as EX
The general premise seems to be that the 1st row of your data is the column header and the data falls underneath:
columnheadera,columnheaderb,columnheaderc
data1,data2,data3
Is there any way to interact with something like this:
ACCTNUMB .. 223910
LOCOPT .... 0
PAYAMT .... -494.78
WARRNTNO .. 01
SOURCE .... 1
ENTRYDAT .. 20040705
SUBACCT ... 224170
SALOCOPT .. 0
TRANCODE .. A
BUDCODE ...
VENDRNUM ..
CLASS ..... 0
CONTRNUM .. 40

Each record is 14 lines apart (13 + blank)

Right now I am doing an interrim step of parsing thru this file and generating a csv. I'd like to see if I could just bypass that step.

thanks,
Chris

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Create a format file containing the following

8.0

13

1 SQLCHAR 0 255 "\r\n" 1 ACCTNUMB

2 SQLCHAR 0 255 "\r\n" 2 LOCOPT

3 SQLCHAR 0 255 "\r\n" 3 PAYAMT

4 SQLCHAR 0 255 "\r\n" 4 WARRNTNO

5 SQLCHAR 0 255 "\r\n" 5 SOURCE

6 SQLCHAR 0 255 "\r\n" 6 ENTRYDAT

7 SQLCHAR 0 255 "\r\n" 7 SUBACCT

8 SQLCHAR 0 255 "\r\n" 8 SALOCOPT

9 SQLCHAR 0 255 "\r\n" 9 TRANCODE

10 SQLCHAR 0 255 "\r\n" 10 BUDCODE

11 SQLCHAR 0 255 "\r\n" 11 VENDRNUM

12 SQLCHAR 0 255 "\r\n" 12 CLASS

13 SQLCHAR 0 255 "\r\n\r\n" 13 CONTRNUM

CREATE TABLE #temp

(ACCTNUMB varchar(255),

LOCOPT varchar(255),

PAYAMT varchar(255),

WARRNTNO varchar(255),

SOURCE varchar(255),

ENTRYDAT varchar(255),

SUBACCT varchar(255),

SALOCOPT varchar(255),

TRANCODE varchar(255),

BUDCODE varchar(255),

VENDRNUM varchar(255),

CLASS varchar(255),

CONTRNUM varchar(255))

BULK INSERT #temp FROM 'filename' WITH (FORMATFILE = 'test.fmt')

SELECT

RTRIM(SUBSTRING(ACCTNUMB,13,255)) AS [ACCTNUMB],

RTRIM(SUBSTRING(LOCOPT,13,255)) AS [LOCOPT],

RTRIM(SUBSTRING(PAYAMT,13,255)) AS [PAYAMT],

RTRIM(SUBSTRING(WARRNTNO,13,255)) AS [WARRNTNO],

RTRIM(SUBSTRING(SOURCE,13,255)) AS [SOURCE],

RTRIM(SUBSTRING(ENTRYDAT,13,255)) AS [ENTRYDAT],

RTRIM(SUBSTRING(SUBACCT,13,255)) AS [SUBACCT],

RTRIM(SUBSTRING(SALOCOPT,13,255)) AS [SALOCOPT],

RTRIM(SUBSTRING(TRANCODE,13,255)) AS [TRANCODE],

RTRIM(SUBSTRING(BUDCODE,13,255)) AS [BUDCODE],

RTRIM(SUBSTRING(VENDRNUM,13,255)) AS [VENDRNUM],

RTRIM(SUBSTRING(CLASS,13,255)) AS [CLASS],

RTRIM(SUBSTRING(CONTRNUM,13,255)) AS [CONTRNUM]

FROM #temp

DROP TABLE #temp

Far away is close at hand in the images of elsewhere.
Anon.

Rick Sheeley SSC Rookie Points: 40 More actions · Answer 2

or, you can use the fixed format method similar to the method you are using now:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & strPathtoTextFile & ";" & _

"Extended Properties=""text;HDR=YES;FMT=FixedLength"""

Fixed length assumes fixed column lengths (which you have), so you should be able to manipulate it just as if it were a table.

Rick

Christopher Klein SSCrazy Points: 2648 More actions · Answer 3

I'm getting an error msg on the version using the format file;

Bulk Insert fails. Column is too long in the data file for row 1, column 13. Make sure the field terminator and row terminator are specified correctly.

if I specify \r\n it treats the blank line like a row and offsets all the records

if I specify \r\n\r\n it gives me the above error.

Any thoughts?

thanks,

Chris

EDIT: nevermind... the actual formatfile needs to have a carriagereturn/lf at the end it seems... works now