Importing a table into SQL

The .dat format usually means binary data. What type of database is this coming from?

>>Will this cause a problem?<
There should not be any problem if the file is in "a fixed flat file ASCII format"

>>How would I import the file?<<
Exactly the same way you import any fixed length text file.

I believe a DB2 format. It is coming from Easytrieve. And is a fixed flat file ASCII. They said each line is distinguished by a Return. When I try loading that into the SQL Database. It at first tells me the Return is not found in the first 8K of data. This could be possible so I click ok and it overlapd data onto each other.

You need to define "Return"  Is it
1. CR + LF: CHAR(13) + CHAR(10)
2. LF: CHAR(10)

In either case SQL Server can handle it, but you must set this yourself: SQL Server will not guess for you.

Also, please followup on these abandoned questions:
2 11/18/2004 250 Floating Bar Graph Problem  Open Microsoft Excel
3 02/09/2005 125 Lookup Table  Open Microsoft SQL Server

You were reminded just last week about this.

This is the response I received:

The row delimiter on the file would be a standard ASCII return line
character field (hex 0d0a).

>>hex 0d0a<<
Right, that is a CR + LF or CHAR(13) + CHAR(10)

You should select that row delimiter when you are doing an import.

When I import the file, set it as a text file as the data source (although it is a .dat file -- is this correct), choose  fixed file and CR + LF as the delimiter and keep the file type as ANSI. It gives me an error message stating "Could not find the selected row delimiter within the first 8KB of data.  Is the selected row delimiter valid?" I click yes since I did verify this.  Then on the next screen where I can split apart the rows the data is on top of each other. It looks normal but shortly after it starts overlapping. I did not put the breaks in the table, since I cannot do all the fields. It starts importing the table, but after a long while I receive an error message that states Failed to copy 1 table(s). The error states Error at Source for Row number 1.  Errors encountered so fat in this task: 1. Error creating file mapping view: Not enough storage is available to process this command. How can I mark when the splits are if it cannot find the delimiter and the data is overlapping each other. Is it because the file is .dat?

Thanks,

Ryan

>>although it is a .dat file -- is this correct<<
Yes.

>>It gives me an error message stating ...<<
There is something wrong in the data file. I suggest you open it in a hex editor and search for the row delimiter.

>>Is it because the file is .dat?<<
The file extension is irrelevant.

When I try to open it with a hex editor, I receive another error. Can't allocate additional memory to perform operation. Do you have any other hex editors you could suggest?

This is what the file layout states:

OUTPUT DCB = RECFM= FB    LRECL=9000 BLKSIZE=27000  

Not sure if this means anything or not...

Thanks,

Ryan

How big is the file?  I use UltraEdit, but there are many others (Textpad comes to mind).  Feel free to Zip it up and send it to me as an attachment (my email address is in my profile) and I will take a look at it.

UltraEdit allows me to open the file without any problems. I contacted the vendor to see where the position break is. It looks like the length goes out about about 4097 positions before it continues on the next line. Am I doing something wrong? Why will it not load? And why does it overlap the data?

Thanks,

Ryan

>>Am I doing something wrong? Why will it not load? And why does it overlap the data?<<
Nope.  It just adds weight to the theory that there is no line break (defined by CR + LF).

Try confirming by toggling to Hex mode and search for 0D 0A.  I suspect you will not find any.

Well I went to the hex editior and it states that 0D 0A is not found. I just did search. Is this correct? Or do I need to check one of the options.

Thanks,

Ryan

>>I just did search. Is this correct? <<
Yes.

>>Or do I need to check one of the options.<<
No.

Open a small text file with UltraEdit that you know has a CR LF and do the same and you will see it finds it just fine.

I think you need to go back and question this:
"The row delimiter on the file would be a standard ASCII return line character field (hex 0d0a)"

I have a small sample. Can I send it to you? With ultraedit you can see that the two samples takes up several lines but it is only 2 records. Can you help?

Thanks,

Ryan

>>Can I send it to you?<<
Sure, just make sure you include EE or something in the subject of the email.

What is your email. I just found out that the reason SQL cannot import the file is because some text is embedded.

Thanks,

See my previous comment:
"Feel free to Zip it up and send it to me as an attachment (my email address is in my profile) and I will take a look at it."

I took a look at your file and I now see the problem.  What is happening is that the row is 9987 bytes long and DTS cannot handle anything mre than 8KB.  What you will have to do is import it using either BCP or Bulk Insert into a temporary table containing a text column and then extract the information from there using either DTS or a stored procedure.  The reason you need a text column is that tables cannot exceed a width of 8060 bytes, and if you use a text column you are only using a 16-byte pointer.

How would I import it with BCP or Bulk insert? Is that a special add-on feature?

The file is in fixed format, so will bcp still work?

In order for the bcp and BULK INSERT utilities to insert data, the data file must be in row and column format.

from msdn

Or am I suppose to insert the whole column into 1 temp table?

When trying to import the data, I have received the error about the interface. I am guessing this means my version of SQL does not have bcp. Do will I have to use bulk import?

Thanks,

Ryan

They are included in the SQL Server components. You can use them from SQL Query Analyzer.  For example:

Create Table #Temp (Data text)

BULK INSERT #Temp
   FROM 'c:\temp\sample.txt'
   WITH (ROWTERMINATOR = '\r')

Select * from #Temp

Drop Table #Temp

I get the following error when I try to run it...

Server: Msg 4834, Level 16, State 1, Line 3
You do not have permission to use the BULK INSERT statement.

This is the code I am using:

Create Table [2005 Passport].[dbo].[Temp] (Data text)

BULK INSERT [2005 Passport].[dbo].[Temp]
   FROM 'c:\temp\C105-FLATMSTR.txt'
   WITH (ROWTERMINATOR = '\r')

Select * from [2005 Passport].[dbo].[Temp]

Drop Table [2005 Passport].[dbo].[Temp]

>>You do not have permission to use the BULK INSERT statement.<<
Right.  You need to belong to the sysadmin permissions role to execute BULK INSERT.

From BOL
<quote>
Only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.
</quote>

Now I receive the path cannot be found. I checked several times and it is in the c:\temp\ directory.

Any suggestions?

If you are running this from SQL Query Analyzer it needs to be on your workstation not the server.

P.S.  It tested fine with me.

I have the file on my personal computer c:\ should I move it to the same computer the server is on?