?
Solved

Importing a table into SQL

Posted on 2005-03-14
32
Medium Priority
?
438 Views
Last Modified: 2012-06-21
I am having a problem importing a table into SQL Server. The table was sent to us as a fixed flat file ASCII format. The one problem I see is the file is in a .dat format instead of a .txt or .csv. Will this cause a problem?

How would I import the file?

Thanks,

Ryan
0
Comment
Question by:rtgaglia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 13
32 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 13539083
The .dat format usually means binary data. What type of database is this coming from?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13539101
>>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.
0
 

Author Comment

by:rtgaglia
ID: 13540003
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13540330
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13540343
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.
0
 

Author Comment

by:rtgaglia
ID: 13556324
This is the response I received:

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

Expert Comment

by:Anthony Perkins
ID: 13557021
>>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.
0
 

Author Comment

by:rtgaglia
ID: 13576066
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13576317
>>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.
0
 

Author Comment

by:rtgaglia
ID: 13576669
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13576862
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.
0
 

Author Comment

by:rtgaglia
ID: 13578943
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13579098
>>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.
0
 

Author Comment

by:rtgaglia
ID: 13579166
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13579323
>>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)"
0
 

Author Comment

by:rtgaglia
ID: 13603158
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13603825
>>Can I send it to you?<<
Sure, just make sure you include EE or something in the subject of the email.
0
 

Author Comment

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

Thanks,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13604013
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."
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13606036
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.
0
 

Author Comment

by:rtgaglia
ID: 13606086
How would I import it with BCP or Bulk insert? Is that a special add-on feature?
0
 

Author Comment

by:rtgaglia
ID: 13606164
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13606176
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
0
 

Author Comment

by:rtgaglia
ID: 13606406
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]
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13606630
>>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>
0
 

Author Comment

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

Any suggestions?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13614427
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.
0
 

Author Comment

by:rtgaglia
ID: 13614543
I have the file on my personal computer c:\ should I move it to the same computer the server is on?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 13614722
>>should I move it to the same computer the server is on?<<

No. If you are running the script from SQL Query Analyzer than the file should be on your workstation.  So if the file is in the c:\Temp folder and is called sample.txt than this worked for me:

Create Table #Temp (Data text)

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

Select * from #Temp

Drop Table #Temp
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question