Setting Column Width in Logparser Created Table

etllogparser

I'm trying to use LogParser for ETL. I am trying to import this file with the following command:

"c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, Field2 AS Zip INTO ZipCodes FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -createtable -cleartable

It creates a table like so:

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [varchar](255) NULL,
    [Zip] [int] NULL
);

If I change the command to

"c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, TO_STRING(Field2) AS Zip INTO ZipCodes FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -createtable -cleartable

It creates the following table:
CREATE TABLE [dbo].[ZipCodes](
[CountryCode] varchar NULL,
[Zip] varchar NULL
);

So TO_STRING(Field2) AS Zip INTO ZipCodes creates [Zip] [varchar](255) NULL.

My goal however, is for the resulting table to be:

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [char](2) NOT NULL,
    [Zip] [char](5) NOT NULL
);

Both columns remain VARCHAR(255) NULL with a SUBSTR like so:

SUBSTR(Field1, 0, 2) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip

How do I get LogParser to create the table with fixed length NOT NULL char columns?

Best Answer

Its possible to get somewhat close to this. First precreate the table

IF object_id('[LogparserTest].[dbo].[ZipCodes]') IS NOT NULL
BEGIN 
    DROP TABLE [dbo].[ZipCodes]
END

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [varchar](3) NOT NULL,
    [Zip] [varchar](6) NOT NULL
);

Note that you have to use varchar columns one character wider than your input.

Now run this command, note the lack of -createtable, but you can keep that there if you desire. It does't recreate an existing table:

"c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT SUBSTR(Field1, 0, 1) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip INTO ZipCodes FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -cleartable -transactionRowCount:0 -ignoreMinWarns:OFF

If you create the correct length columns as char or varchar you get the following error for every row:

An error occurred while uploading data to SQL table
  SQL State:     22001
  Native Error:  0
  Error Message: [Microsoft][ODBC SQL Server Driver]String data, right
  truncation

However, note that SELECT MAX(LEN([CountryCode])), MAX(LEN(ZIP)) FROM [LogparserTest].[dbo].[ZipCodes]; Returns 2 and 5. Tracing the bulk insert with extended events shows that things are being paramaterized as follows so I don't know what's going on:

(@Param000004 char(2),@Param000005 char(5))INSERT [LogparserTest].[dbo].ZipCodes VALUES(@Param000004,@Param000005)

So at this point you still need to perform an ALTER TABLE ALTER COLUMN command to alter each column, so it doesn't actually buy you much.

Related Question