Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk insert from file having varying number of columns Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2013 2:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 7:34 AM
Points: 28, Visits: 74
I have a csv file having different number of columns in each row.
I will have to dump this data into a staging table having 20 columns.

20131111,010103,1,Test , Test ,"HFirst",43,40,42
20131111,010113,2,"Test HScriptTwo",43,40
20131111,010123,3,"Testing HThree","HScript Three"

It should store the data leaving the rest of the columns as NULL.
It is currently filling all the 20 columns ignoring the line break, Writing to the columns continously.

BULK INSERT SysRepTemp1
FROM 'c:\SystemReports.csv'
WITH
(
FIRSTROW=1,
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
Post #1513365
Posted Tuesday, November 12, 2013 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 12,965, Visits: 32,525
Dorthy Jeff Moden has a couple of posts on this, which i'd saved in the past:
here's the specific threads in question:
http://www.sqlservercentral.com/search/?q=%22Headerless+Ragged+Right%22

basically, he uses some dos commands to make sure the file has headers if it didn't already, and then uses a text-file linked server which automatically treats the files as having NULLS for missing columns(which in turn make the columns for the import somewhere else.)


those old posts refer to 32 bit linked servers usign the JET driver, so if you need a 64 bit text based linked server, it's like this example:
select * from OpenRowset('MSDASQL', 
'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\Data\;'
,'select top 10 * from C:\Data\MailItems.txt')



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1513438
Posted Tuesday, November 12, 2013 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 7:34 AM
Points: 28, Visits: 74
Thank you Jeff
Post #1513454
Posted Tuesday, November 12, 2013 8:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 35,798, Visits: 32,478
Lowell (11/12/2013)
Dorthy Jeff Moden has a couple of posts on this, which i'd saved in the past:
here's the specific threads in question:
http://www.sqlservercentral.com/search/?q=%22Headerless+Ragged+Right%22

basically, he uses some dos commands to make sure the file has headers if it didn't already, and then uses a text-file linked server which automatically treats the files as having NULLS for missing columns(which in turn make the columns for the import somewhere else.)


those old posts refer to 32 bit linked servers usign the JET driver, so if you need a 64 bit text based linked server, it's like this example:
select * from OpenRowset('MSDASQL', 
'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\Data\;'
,'select top 10 * from C:\Data\MailItems.txt')



Sadly, there are 3 things that are now wrong with the method I posted... 32 bit limitations, performance, and privs. A flat load followed by a split and a crosstab are frequently more effective not to mention the fact that OPENROWSET requires "SA" privs to run. I'll see if I have the time to demo some code after work.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1513477
Posted Tuesday, November 12, 2013 8:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 12,965, Visits: 32,525
here's my linked server example for a 64 bit folder full of text files that may help:

a couple of prerequisites:

install the AccessDatabaseEngine_x64.exe from microsoft:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
command line flag;

this will force the install of the drivers, even if you have 32 bit office installed;
otherwise you get some error about 32 bit Office preventing the install.
After that is installed:
--Required settings for the provider to work correctly as a linked server
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO


and then the code for the text based linked server:
--#################################################################################################
--Linked server Syntax for Folder Full Of Text Files
--#################################################################################################

--add a folder as a linked server to access all .txt and .csv files in the folder
DECLARE @server sysname,
@srvproduct nvarchar(256),
@provider nvarchar(256),
@datasrc nvarchar(100),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
SET @server = N'TxtSvr'
SET @srvproduct = N'OLE DB Provider for ACE'
SET @provider = N'Microsoft.ACE.OLEDB.12.0'
SET @datasrc = N'C:\Data\'
SET @provstr ='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\;Extended Properties="text;HDR=YES;FMT=Delimited" '
set @provstr = 'Text'

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,@provstr,@provstr
GO
--===== Set up login mappings.
EXEC dbo.sp_AddLinkedSrvLogin TxtSvr, FALSE, NULL, Admin, NULL
GO
--===== List the tables in the linked server which is really a list of
-- file names in the directory. Note that the "#" sign in the
-- Table_Name is where the period in the filename actually goes.
EXEC dbo.sp_Tables_Ex TxtSvr
GO
--===== Query one of the files by using a four-part name.
SELECT *
FROM TxtSvr...[LegalName_NickName_List#txt]

--===== Drop the text server
EXEC dbo.sp_DropServer 'TxtSvr', 'DropLogins'
GO



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1513493
Posted Tuesday, November 12, 2013 9:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 35,798, Visits: 32,478
Lowell (11/12/2013)
here's my linked server example for a 64 bit folder full of text files that may help:


Very cool. I've not actually used the ACE drivers, yet (had other problems that would take too long to explain) but now I have a great example for how to use them to solve the ragged-right problem.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1513536
Posted Tuesday, November 12, 2013 10:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 12,965, Visits: 32,525
Jeff Moden (11/12/2013)
Lowell (11/12/2013)
here's my linked server example for a 64 bit folder full of text files that may help:


Very cool. I've not actually used the ACE drivers, yet (had other problems that would take too long to explain) but now I have a great example for how to use them to solve the ragged-right problem.


yeah i went to some effort to update all my 32 bit examples that i collected over the years, and update them to work in the 64 bit environment too. there wasn't enough examples out there of working 64 bit linked servers.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1513540
Posted Wednesday, November 13, 2013 1:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 7:34 AM
Points: 28, Visits: 74
Thanks Jeff for your solution.

Isn't there a way to get this through without using linked server? A little simpler solution?
Please let me know.
Post #1513726
Posted Saturday, November 23, 2013 4:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 35,798, Visits: 32,478
Sorry for the late response. I lost track of this thread. Do you still need help on this?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1517049
Posted Wednesday, March 12, 2014 12:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:52 AM
Points: 157, Visits: 377
Re: 64-bit ACE & 32-bit Office apps...

If you install this, and you have 32-bit Office apps, it will probably mess things up for the Office apps. At least that's been my experience with 32-bit Office 2010 apps on 64-bit Windows 7.0. I wanted to work within 64-bit SQLExpress 2008R2, and thus installed 64-bit ACE engine (with the switch to do so), and it did indeed make the 64-bit ACE engine visible to SQL Express, but then...

Excel 2010 - wanted to reinstall/reconfigure each start (and it made PowerQuery unavailable...)
Project 2010 - wanted to reinstall/reconfigure, then failed ("out of memory"...really?)
Word 2010 - wanted to reinstall, but the NoReReg=1 registry change worked for Word.

"NoReReg = 1" did not work for Excel or Project...

So..... I installed 32-bit SQL Express, uninstalled 64-bit ACE, and... Office apps are OK now.

Reinstalled PowerQuery just to be safe.

On a 64-bit SQL Server box, having 32-bit and 64-bit ACE installed, seemed to work... the 32-bit ACE engine is not visible to SQL Server, but the 64-bit engine will be.

Post #1550375
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse