Best Way to Import Non-Standard Text Files into SQL Server

etlsql serversql-server-2012

I have a text file that is generated daily. It is a fixed width format with one frustrating exception. The third column (NAME) has a variable width. The column is always exactly as long as the longest name in the data set + 1 space. This makes each day's files have different column start and end points.

Does anyone have a clever way to handle importing this file on a daily basis? Changing the source program is not an option.

I am willing to modify the file before import.

I am willing to use almost any tool to include, but not limited to, SSIS, C#, PowerShell.

I am looking for the least "messy" solution with high-reliability.

Examples

A/R                                      Patient                                  PICASI, LINDA  00691685             90409843             Inpatient                                


Revenue                                  Patient                                  SIMPSON, RHASIRA B 00000000             00000000             Emergency

Best Answer

Based on your sample data it looks like there are 6 columns in total.

COL1, COL2, COL4, COL5, COL6 = All fixed width .

COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding).

You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed width. Take the line total and subtract COL1,2,4,5,6 to get COL3's width each day.

COL3 = LINE_WIDTH - (COL1 + COL2 + COL4 + COL5 + COL6)

After you get COL3's width you will be able to figure out all of your start and end points.

Related Solutions

Sql-server – the fastest way to export a table to a text file

Looping through all those articles is an option if you want to get old watching it execute.

Some different options that you should try are:

the bcp utility (mentioned above, too, it's a command line tool to import and export data to/from an SQL Server);
BULK INSERT statement - the T-SQL language sugar to work with data imports from scripts;
SqlBulkCopy class from .NET - if you want to work from inside the app. A nice example can be seen here: Linchi Shea's Performance Impact: the Most Optimal Insert Script can't Beat BulkCopy .

And you can try all these options while looping in another session, just for fun :-).

SSIS – How to Parse Extensions from Path-Filename Strings

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )

In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:

Remove any trailing white-space with RTRIM( name )
Reverse the string so we are dealing with the end as the beginning with REVERSE()
Grab the first "token" delimited by a backslash using TOKEN()
Count how many "." delimiters are in the result using TOKENCOUNT()
Test the count of "." delimited tokens is equal to 1 using ?
If the count is 1, return an empty string - no extension.
Otherwise return the first token before the "." using TOKEN()
Reverse the extension to get back to "normal" using REVERSE()

Best Answer

Related Solutions

Sql-server – the fastest way to export a table to a text file

SSIS – How to Parse Extensions from Path-Filename Strings

Related Question