March 2, 2009 at 2:35 am
Hi,
I have a txt file with comma field terminator:
e.g.
1, tablname1, datacolumn1, datacolumn2, datacolumn1
1, tablname2, 1, datacolumn2, 2, 3
1, tablname2, datacolumn1, datacolumn2, datacolumn1
1, tablname1, datacolumn1, datacolumn2, datacolumn1
1, tablname2, 1, datacolumn2, 2, 3
I want to grab this files and put it on a recordset or temp table...is this possible?
like:
SELECT * INTO #Sample
FROM
OPENROWSET(BULK N'\\mydir\myfile.txt', SINGLE_BLOB) As MyDoc;
On the above example, I can see that the data has been some sort of encrypted. Are there any alternative to this? I just want to simply copy the data from a text containing different data for different tables and load it on a "temporary table".
March 2, 2009 at 2:37 am
Search BOL for BCP.
"Who then will explain the explanation? Who then will explain the explanation?" Lord Byron
March 2, 2009 at 3:29 am
Most chances are that you see it as varbinary. See what happens if you use convert function to convert it to varchar of if you use single_clob instead of single_blob.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 2, 2009 at 5:34 am
Thanks for the reply guys. I have been searching about BULK INSERT, OPENROWSET, OPENXML, BCP and etc on the net but can't find the exact way on how to do the requirement that I am about to achieve. If you can give me a sample working code that would do the task then I would really appreciate it since I am running out of time researching.
Thanks for the help.
March 3, 2009 at 3:59 pm
------'C:\Test\TestBulkInsert.txt' -----
--1, tablname1, datacolumn1, datacolumn2, datacolumn1,
--1, tablname2, 1, datacolumn2, 2, 3
--1, tablname2, datacolumn1, datacolumn2, datacolumn1,
--1, tablname1, datacolumn1, datacolumn2, datacolumn1,
--1, tablname2, 1, datacolumn2, 2, 3
drop table #Sample
create table #Sample(
f1 int,
f2 sysname,
f3 sysname,
f4 sysname,
f5 sysname,
f6 sysname
)
BULK INSERT #Sample
FROM 'C:\Test\TestBulkInsert.txt'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
FIRSTROW = 1
)
select * from #Sample
March 3, 2009 at 9:29 pm
gamma (3/3/2009)
------'C:\Test\TestBulkInsert.txt' -------1, tablname1, datacolumn1, datacolumn2, datacolumn1,
--1, tablname2, 1, datacolumn2, 2, 3
--1, tablname2, datacolumn1, datacolumn2, datacolumn1,
--1, tablname1, datacolumn1, datacolumn2, datacolumn1,
--1, tablname2, 1, datacolumn2, 2, 3
drop table #Sample
create table #Sample(
f1 int,
f2 sysname,
f3 sysname,
f4 sysname,
f5 sysname,
f6 sysname
)
BULK INSERT #Sample
FROM 'C:\Test\TestBulkInsert.txt'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
FIRSTROW = 1
)
select * from #Sample
How can I do bulk insert into a temporary table without specifying columns from time to time? since on that text file, it may contain tables with different number of columns.
like a SELECT * INTO #Sample perhaps ......
Thanks for the help
March 4, 2009 at 12:32 pm
Short answer is NO,you can't.
You have to create your table (temp ot static)first.
If you do not have a stable data pattern in your text file how useful will be that "generic" temp table?
You can select from such table but for join you need to know what column to join to.
March 4, 2009 at 6:25 pm
gamma (3/4/2009)
Short answer is NO,you can't.You have to create your table (temp ot static)first.
If you do not have a stable data pattern in your text file how useful will be that "generic" temp table?
You can select from such table but for join you need to know what column to join to.
Thank you for you helpful thoughts. Maybe you can help me on what I am trying to achieve since I am doing it the wrong way.
So, i have a text file which contains comma separated data..each data has their own table (table code) on where they should be inserted to. So on one text file there is many table inside it with certain data for each table. How do I read that file and insert the data in to its respective table? each table has different number of columns.
I am using SQL Server 2005 Express Edition, they were saying that I can use SSIS for this but not on Express Edition. If Bulk Insert, OpenRowset, OpenQuery, or BCP is the alternative, can you give a sample on how to do such process?
Thank you for the response.
March 4, 2009 at 10:16 pm
If you can’t use SSIS, then you can insert the file into a table that has only one column. After that you can read each row from your temporary table and apply the logic and insert the data to its designated table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2009 at 2:38 am
Adi Cohn (3/4/2009)
If you can’t use SSIS, then you can insert the file into a table that has only one column. After that you can read each row from your temporary table and apply the logic and insert the data to its designated table.Adi
So you are saying that without the presence of SSIS, there is no alternative to insert one file with multiple tables to the database?
March 5, 2009 at 1:00 pm
No this is not what I’m saying. You need to write the logic that will know what to do with each line. You can use SSIS for that. You can also write a small program or script (I’m talking about VBscript, perl or any other scripting language. I’m not talking about SQL script) that opens the file reads each line and then according to the data in the line will know which table and columns to use. You can also write this logic with T-SQL, but if you are going to do all of this with T-SQL, it will be much easier to load the data first into a staging table instead of working directly with the file.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2009 at 1:21 pm
[font="Verdana"]SQL Server Express is a very limited edition of SQL Server, primarily intended as a back-end for MS Access and for small web sites and the like. It's not intended as "serious" development platform (judging by how crippled Microsoft have made it.)
You may want to consider upgrading to a real version of SQL Server, where you can use the real tools for work like this.
On the other hand, as I have some familiarity with SSIS, this will only move your problem, not solve it.
The best suggestion I can come up with for now is to bulk insert (or use openrowset()) the data into a table containing one field of varchar(max) (or nvarchar(max) if you need unicode) and parse the data once it's in the database. Note that this will be slow. You would be better predefinding the columns that you get in the text file, so you could load it straight into a parsed table.
[/font]
March 5, 2009 at 5:58 pm
Adi Cohn (3/5/2009)
No this is not what I’m saying. You need to write the logic that will know what to do with each line. You can use SSIS for that. You can also write a small program or script (I’m talking about VBscript, perl or any other scripting language. I’m not talking about SQL script) that opens the file reads each line and then according to the data in the line will know which table and columns to use. You can also write this logic with T-SQL, but if you are going to do all of this with T-SQL, it will be much easier to load the data first into a staging table instead of working directly with the file.Adi
Actually that is what my other option is, loading the data into a staging table. But I just don't know how to do bulk load with different number of columns inside a text file because you cannot simply use SELECT * INTO #Temp....
Bruce W Cassidy
We have an xml configuration that contains the definition per table. Is there a way that I can automatically create a temp table using our xml configuration? because inside a text only contains the table code and I still need to lookup to that xml configuration for its corresponding name including the number of columns and the name per column.
Thank you for the input.
March 5, 2009 at 7:09 pm
[font="Verdana"]Maaaaybe. The XML options differ from version to version of SQL Server, and if your file is a plain text file, getting the XML wrapper to somehow wrap around it would be tricky (to say the least). How about you give it a go and let us know how you get on?[/font]
March 5, 2009 at 11:34 pm
Actually that is what my other option is, loading the data into a staging table. But I just don't know how to do bulk load with different number of columns inside a text file because you cannot simply use SELECT * INTO #Temp....
You misunderstood me. You can create a table with one column. Each line in the file will be inserted as a whole row into the table. Then you can write your own code to analyze the row and insert the data into the real table. By the way, since you already mentioned XML, if you could get this data as XML instead of regular text file, you could have used the XML bulk load utility to insert the data from the text file directly into the different tables.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply