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 csv file to SQL Server

Can someone please help me.
I need to import a csv fiel to sql server and I know that the column delimiter is \t
and the newline delimiter is \n but I don't know what the rowterminator is or the fieldterminator. How can I import the file into an empty table in an existing database.
Any suggestions would be greatly appreciated.Use DTS to import the file it can handle it all, including create a new empty table from the file.
You can do it directly in Enterprise Manager, select the database and use right click > All Tasks > Import Data

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.