July 9, 2010 at 10:14 am
Hi,
I use a OpenRowset Query to create a table in SQL Server from .txt file.
The command don't show errors, but the table does not have all the columns. It only appears 255. The original file has 623 columns.
The example is:
SELECT * INTO TB_TEMPORAL
FROM openrowset ('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\MG\;','SELECT * FROM XXX.txt')
I need to use something similar because the columns may varies in the .txt.
Thanks for your help.
July 9, 2010 at 3:20 pm
try using ssis if that imports all the columns... how big is one record?
July 9, 2010 at 3:27 pm
Thanks.
Yes, SSIS is an option, but the txt files varies from load to load (less o more columns). For this reason is not posible use a SSIS.
Any ideas?
July 10, 2010 at 3:03 am
Can’t you request for a common standard for the text , csv file? If you can initiate this SSIS is the way forward
July 10, 2010 at 7:19 am
It looks like if you change your provider to SQL Native Client, that it will work.
I ran this code in a database named "Sandbox", and both queries return the 600 columns that I'm creating.
if object_id('dbo.test') IS NOT NULL DROP TABLE dbo.Test
declare @Sql varchar(max)
;WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
SELECT @sql = 'CREATE TABLE dbo.Test (' +
STUFF((SELECT TOP (600) ',COL' + convert(varchar(3), N) + ' INT'
FROM TALLY
ORDER BY N
FOR XML PATH('')),1,1,'') + ')'
exec (@sql)
select * from dbo.TEST
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=WS-HPDV7\SQL2008DEV;Trusted_Connection=yes;',
'SELECT *
FROM Sandbox.dbo.Test') AS a;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 12, 2010 at 6:49 am
Thanks for your reply. I don't request a standard text file because is generated by a closed application. It's depends of the results of polls (less o more answers). This is the reason why the number of columns vary from load to load.
July 12, 2010 at 9:29 am
Did changing to the SQL Native Client driver help out? As I demonstrated, it seems to work for me.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 12, 2010 at 9:47 am
WayneS (7/12/2010)
Did changing to the SQL Native Client driver help out? As I demonstrated, it seems to work for me.
Wayne i thought the OP was running into the fact that the JET text driver is limited to 255 columns; while the SQL native driver supports more, he was trying to import from text file and not another server, right?
everything i googled said it was possible to get around the 255 CHAR to a column fix with IMEX=1, but no (direct) solution to 255 columns max for the JET driver.
i don't think creating a schema.ini file will help either, as it's a limitation to JET on the # cols.
i think he'll need to bulk insert into a staging table, and then use something like your solution after it is in SQL.
Lowell
July 12, 2010 at 10:04 am
Lowell (7/12/2010)
WayneS (7/12/2010)
Did changing to the SQL Native Client driver help out? As I demonstrated, it seems to work for me.Wayne i thought the OP was running into the fact that the JET text driver is limited to 255 columns; while the SQL native driver supports more, he was trying to import from text file and not another server, right?
everything i googled said it was possible to get around the 255 CHAR to a column fix with IMEX=1, but no (direct) solution to 255 columns max for the JET driver.
i don't think creating a schema.ini file will help either, as it's a limitation to JET on the # cols.
i think he'll need to bulk insert into a staging table, and then use something like your solution after it is in SQL.
Thank you Wayne, but the solution not working, because is a text file.
July 12, 2010 at 10:05 am
leonardo.castillo (7/12/2010)
Lowell (7/12/2010)
WayneS (7/12/2010)
Did changing to the SQL Native Client driver help out? As I demonstrated, it seems to work for me.Wayne i thought the OP was running into the fact that the JET text driver is limited to 255 columns; while the SQL native driver supports more, he was trying to import from text file and not another server, right?
everything i googled said it was possible to get around the 255 CHAR to a column fix with IMEX=1, but no (direct) solution to 255 columns max for the JET driver.
i don't think creating a schema.ini file will help either, as it's a limitation to JET on the # cols.
i think he'll need to bulk insert into a staging table, and then use something like your solution after it is in SQL.
Thank you Wayne, but the solution not working, because is a text file.
Sorry Lowell, but you have a example of "staging table"'?
July 12, 2010 at 12:05 pm
i'm thinking something like this:
bring everything is as one big field...i'm assuming varchar(8000) would hold it, but it might need varchar(max).
once in a table, use CHARINDEX2 to chop everything up into642 varchar columns....i'd create a view to make it easy, with cols F001 thru F642 or whatever it takes.
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION CHARINDEX2
(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int
)
RETURNS int
as
begin
declare @pos int, @counter int, @ret int
set @pos = CHARINDEX(@TargetStr, @SearchedStr)
set @counter = 1
if @Occurrence = 1 set @ret = @pos
else
begin
while (@counter < @Occurrence)
begin
select @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
set @counter = @counter + 1
set @pos = @ret
end
end
RETURN(@ret)
end
GO
CREATE TABLE STAGINGTABLE(RAWDATA VARCHAR (8000))
BULK INSERT STAGINGTABLE FROM 'C:\MG\XXX.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = '~', --a delimiter that does nto exist in the data
ROWTERMINATOR = '{slash n}', --the real slash n cannot be displayed in the forum
FIRSTROW = 1
)
--build this with excel or something for 623 fields:
SELECT
SUBSTRING(RAWDATA,1,dbo.CHARINDEX2(',',RAWDATA,1,1)-1) AS F001,
SUBSTRING(RAWDATA,dbo.CHARINDEX2(',',RAWDATA,1,1)+1 ,dbo.CHARINDEX2(',',RAWDATA,1,2)-1) AS F002
SUBSTRING(RAWDATA,dbo.CHARINDEX2(',',RAWDATA,1,2)+1 ,dbo.CHARINDEX2(',',RAWDATA,1,3)-1) AS F003
...
SUBSTRING(RAWDATA,dbo.CHARINDEX2(',',RAWDATA,1,641)+1 ,dbo.CHARINDEX2(',',RAWDATA,1,642)-1) AS F641
SUBSTRING(RAWDATA,dbo.CHARINDEX2(',',RAWDATA,1,642)+1,30) --assuming the last field is less than 30 chars.
Lowell
July 12, 2010 at 12:12 pm
Thanks, i will test it today. I will let you know if it works
Lowell (7/12/2010)
i'm thinking something like this:bring everything is as one big field...i'm assuming varchar(8000) would hold it, but it might need varchar(max).
once in a table, use CHARINDEX2 to chop everything up into642 varchar columns....i'd create a view to make it easy, with cols F001 thru F642 or whatever it takes.
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION CHARINDEX2
(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int
)
RETURNS int
as
begin
declare @pos int, @counter int, @ret int
set @pos = CHARINDEX(@TargetStr, @SearchedStr)
set @counter = 1
if @Occurrence = 1 set @ret = @pos
else
begin
while (@counter < @Occurrence)
begin
select @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
set @counter = @counter + 1
set @pos = @ret
end
end
RETURN(@ret)
end
GO
CREATE TABLE STAGINGTABLE(RAWDATA VARCHAR (8000))
BULK INSERT STAGINGTABLE FROM 'C:\MG\XXX.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = '~', --a delimiter that does nto exist in the data
ROWTERMINATOR = '{slash n}', --the real slash n cannot be displayed in the forum
FIRSTROW = 1
)
--build this with excel or something for 623 fields:
SELECT
SUBSTRING(RAWDATA,1,dbo.CHARINDEX2(',',RAWDATA,1,1)-1) AS F001,
SUBSTRING(RAWDATA,dbo.CHARINDEX2(',',RAWDATA,1,1)+1 ,dbo.CHARINDEX2(',',RAWDATA,1,2)-1) AS F002
SUBSTRING(RAWDATA,dbo.CHARINDEX2(',',RAWDATA,1,2)+1 ,dbo.CHARINDEX2(',',RAWDATA,1,3)-1) AS F003
...
SUBSTRING(RAWDATA,dbo.CHARINDEX2(',',RAWDATA,1,641)+1 ,dbo.CHARINDEX2(',',RAWDATA,1,642)-1) AS F641
SUBSTRING(RAWDATA,dbo.CHARINDEX2(',',RAWDATA,1,642)+1,30) --assuming the last field is less than 30 chars.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply