Importing Text File: How to dynamically change the row delimiter

Hi,

I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?

i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.

Any help would be appreciated

I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched

Regards JustinThe solution is to identify and change the EOL character that is used in the file before the file is passed through the dts package. The scripting object doesn't appear to contain anything useful that will identify the EOL character so I have come up with this routine in VBA rather then VBScript. It will accept a user defined number of characters in a text file for and returns the EOL character (only a carriage return or line feed).

Public Function gIdentifyEOLCharacter(strFileName As String, _
lngNumOfCharactersToCheck As Long) As String
' identifies the end of line character
Dim fsoSysObj As Scripting.FileSystemObject
Dim tStream As Scripting.TextStream
Dim strText As String, strEOLCharacter As String
On Error GoTo ErrorHere
Set fsoSysObj = New Scripting.FileSystemObject
Set tStream = fsoSysObj.OpenTextFile(strFileName, ForReading)
strText = tStream.Read(lngNumOfCharactersToCheck)
If InStr(strText, Chr$(13)) Then _
strEOLCharacter = strEOLCharacter & "{CR}"
If InStr(strText, Chr$(10)) Then _
strEOLCharacter = strEOLCharacter & "{LF}"
gIdentifyEOLCharacter = strEOLCharacter
ExitHere:
Set fsoSysObj = Nothing
Set tStream = Nothing
Exit Function
ErrorHere:
MsgBox "Error In: Module 'basSupportFunctions'" & vbCrLf _
& "Procedure: gIdentifyEOLCharacter" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
gIdentifyEOLCharacter = vbNullString
Resume ExitHere
End Function

I can't believe that there is nothing simpler in the scripting object that can return the EOL character - i have looked through the object model and can't see anything that is useful|||I think you can try FINDSTR with /G:file parameter where you'd have nothing but CRLF in the file. If FINDSTR returns a file name then it means that the specified file contains normal EOL combination. Else, - it's not. You can also have 2 files, one with CRLF and the other with just LF. So that if the first one produced 0 results you can do FINDSTR against the second and be certain that LF is the actual delimiter.|||This is just a wild guess but you might run a cmdshell and do:

type inputfile.txt>newfile.txt

before importing. Type may convert LF to CRLF.

I also wonder if you could create a CR placeholder column with a default value of null and always specify LF as the line terminator.

If these files are coming from an FTP transfer, set the type to Ascii instead of bin in FTP to get CRLF terminators.

Importing TEXT File in DTS

Hello Guys,
I Hava a Source text connection and I'd like to take just the first row ( the header, of course) of the file to one table. How can I get this??
Tis is quite Urgent.
Thanxs;Does it have to be DTS?

Why not DTS in to a single column table (varchar(8000)) and the parse out the data in to the final table?|||Because the text files can be larger than 200MB. :(
I need to take just the first Row of the text file to know some important informations.|||You sure you're talking about row size?

That's a long row....|||No. Im speaking about the File.

Look an example of the beggining of the file:
I want to get the first row of the file and put it into a column. Note, just the first row. You can see that the another lines are in a different layout and would make my table very big.

A221539 DPVAT - COD BAR 151BANCO NOSSA
G00000000000000 20040123200401298664000000093373362
G00000000000000 20040123200401298663000000093383362
G00000000000000 20040123200401298669000000051623362
G00000000000000 20040123200401298669000000093383362
G00000000000000 20040123200401298664000000093383362
G00000000000000 20040123200401298661000000093383362
G00000000000000 20040123200401298661000000055433362|||Have you looked at BULK INSERT in BOL?

You can specify first row and last row (ie 1 and 1)

Importing Excel data into SQL Server 2005 with a twist!

Hi,

I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.

However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.

So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.

TIA,

Graham.

I would approach the problem by importing all of the spreadsheet rows and then process to the required table.

Importing ASCII file using DTS with no row delimiter

Hi,

I am trying to import a file using DTS that does not have any row delimiter. It is a file that was exported from an OLD OLD OLD Macintosh database program (now defunct) called TouchBase. The file uses quotes as text seperators, and tabs for the column delimiters.

Unfortunately, from what I can tell, there are now row delimiters, so the data just keeps going and going, ie like this:

"Bob Smith" "1234 Market Street" "San Diego" "CA" "Josh Smith" "1212 Anywhere Street" "San Diego" "CA" "Jane Smith" "1234 Jane Street" "San Diego" "CA"

Any idea how I can get this to import, or some way to add the delimiter to the file?May be you can use third party tools to the text file to add row delimiter.

And refer to this SQLDTS (http://www.sqldts.com/default.aspx?6,101,257,0,1) link to accomplish the task.|||I can't seem to find any 3rd party tools using google. I've opened the file using a freeware hex editor, and used global search and replace to add one, but then I found out that some records meet the search pattern i chose in the middle of the record, not the end, so that screwed the whole file up..

Very frustrating because I have multiple problems with the file:

#1 - The User used CRLF in a field called "Notes" which is just a free flowing field where the user can type as much or as little as they want in a little diary on the record.

#2 - The User also used tabs in the notes fields, which is throwing off the delimiter for the column.

#3 - The User used double quotes " inside the notes field so that is also throwing it off.

UGH.. Not sure how to fix this mess.|||how big is the file?|||File is about 2.5 megs.

Finally got it.

Used a freeware hex/ascii editor called xvi (search google for hex editor, it's the first one that comes up).

What I did first was stip all CRLF out of the file, since they were only in places where they shouldn't be, not at the end of each row.

Then I went through and did a global replace on all "<TAB>" and changed it to {~} (I first made sure she wasn't using those characters any where in the file!)

Then, I went and stripped all the tabs and all the quotes from the file.

Then i went back and replaced the {~} with "<TAB>" so they would be where they were supposed to.

Finally, I went and I added the CRLF to the end of each record. The way I did this was the last field in each record was a "Date last modified". I asked the client if they needed this, and they said no.

So, I took and used the "wildcard" feature in XVI and did a global replace. Searched for:

"MM/DD/YY"<TAB> and replaced with "MM/DD/YY"<CR><LF>

However, it was kinda tricky, I had to actually do 4 global replaces, because it's an old mac database, and it stored the data in M/D/YY format, MM/D/YY format, M/DD/YY format, and MM/DD/YY format, so there were 4 possible combinations of the way the date was stored for date last modified.

Thank god it worked!

I had 4 errors when importing, and of course SQL server aborts the import if there are any errors. I had to go back in the file and scroll down to the aproximate part where the error was, and look for the problem. Turns out there were 4 records that were missing a field, and that was throwing the whole import off, so I added the field in where apropriate, or just deleted the record.

All in all, this job took me about 15 hours to scrub the file, do all my home work, and get everything together.. Definately a great learning experience for me, and VERY rewarding that I finally got her data to her!!!!|||If you need to do alot of this type of, I would get Codewright. I think Borland now owns the product. It is pricey but well worth it.