August 9, 2013 at 4:17 am
Hi all I am tasked with importing large fixed width text file to SQL, previously I used a library called filehelpers to assist through my code. Now the files are growing I keep getting out of memory errors so decided to try BCP.
My command
bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmt
and the result
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: tt open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or specific error has occurred while establishing a connection to SQL Server is not found or not accessible. Check if instance name is correct and Server is configured to allow remote connections. For more information server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
This is a localhost server with windows auth
Please help me learn why, I have tried with the -t switch and same result
August 9, 2013 at 4:41 am
The localhost server, is it the default instance?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2013 at 4:47 am
SSMS connects using localhost\DEV
August 9, 2013 at 4:52 am
You need to specify the server as well in your bcp command line.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2013 at 4:57 am
Try the following link
http://www.sqlteam.com/tools/bcphelper.zip
I use this when creating anything using BCP and saves me a load of time.
August 9, 2013 at 5:01 am
doh! ^^
bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmt -S localhost\DEV
works fine, thanks a lot.
My Next question is using that file is it possible to specify the format of a date time to "M/d/yyyyHH:mm:ss"
August 9, 2013 at 5:05 am
Not an expert in bcp format files, but you can use a query in BCP, so I would do the formatting there.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2013 at 4:13 pm
jerome.morris (2013-08-09)
My Next question is using that file is it possible to specify the format of a date time to "M/d/yyyyHH:mm:ss"
You cannot specify any format for datetime values, but the format will always be YYYY-MM-DD hh:mm:ss.fff. Hm, you can use -R to get the regional settings (of the system locale, as IU recall.) BCP is primary intended to move data between servers. If you need formatting, you need to do that in the query.
But you are probably better off using SSIS. Or go back and fix your program. There is on reason why would get "out of memory" when you write a file. Unless you do something like reading all data in to a DataSet. Use ExecuteReader instead and you will be fine.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 9, 2013 at 8:16 pm
Koen Verbeeck (8/9/2013)
Not an expert in bcp format files, but you can use a query in BCP, so I would do the formatting there.
You can only use a query to export from SQL Server to a file.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2013 at 8:19 pm
jerome.morris (8/9/2013)
doh! ^^bcp SLADB.dbo.AlarmDetTB format nul -T -n -f ProdData-n.fmt -S localhost\DEV
works fine, thanks a lot.
My Next question is using that file is it possible to specify the format of a date time to "M/d/yyyyHH:mm:ss"
Kind of. Before I can answer that question, I have a couple of my own.
1. Will the "M" and "d" always be two digits?
2. Are you using a staging table as the BCP target (if not, I very strongly recommend that you do)?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2013 at 4:34 am
This is some sample Data
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201013:41:48 Engine_Insert 10063Wed Mar 03 13:53:49 2010 20 0 0
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201013:41:48 West_Cutter_FBC_Lake 10086Wed Mar 03 13:55:31 2010 30 340 0
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201013:41:48 West_Cutter 10295Wed Mar 03 14:01:49 2010 31 119 0
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201014:07:26 West_Cutter 10295Wed Mar 03 14:07:46 2010 30 1 0
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201014:07:26 West_Cutter 10295Wed Mar 03 14:08:25 2010 30 24 0
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201014:07:26 West_Cutter 10295Wed Mar 03 14:09:23 2010 34 67 0
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201014:07:26 Engine 10163Wed Mar 03 14:11:10 2010 4 0 0
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201014:07:26 Engine 10052Wed Mar 03 14:11:18 2010 30 12 0
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201014:07:26 West_Cutter 10295Wed Mar 03 14:12:08 2010 10 0 0
APS06 C5_LAKE_WE85912_003_8591200305ENGINEER 03/03/201014:45:40 Engine_Insert 10063Wed Mar 03 14:46:08 2010 13 0 0
APS06 C5_LAKE_WE85912_003_8591200305ENGINEER 03/03/201014:45:40 West_DualFim_Dual_Left 10063Wed Mar 03 14:46:38 2010 10 0 0
APS06 C5_LAKE_WE85912_003_8591200305ENGINEER 03/03/201014:45:40 West_Cutter 10298Wed Mar 03 14:47:03 2010 30 46 0
APS06 C5_LAKE_WE85912_003_8591200305ENGINEER 03/03/201014:45:40 Mailpiece Manager 10012Wed Mar 03 14:48:50 2010 30 422 1
APS06 C5_LAKE_WE85912_003_8591200305ENGINEER 03/03/201014:56:30 Mailpiece Manager 10012Wed Mar 03 14:56:35 2010 30 43 0
APS06 C5_LAKE_WE85912_003_8591200305ENGINEER 03/03/201014:56:30 West_Cutter_FBC_Lake 10004Wed Mar 03 14:58:01 2010 30 173 1
So if I make a table all strings then import this into that table using the bcp command, can I then move what I need another table and convert the data to required formats on the way?
August 10, 2013 at 11:44 am
jerome.morris (8/10/2013)
So if I make a table all strings then import this into that table using the bcp command, can I then move what I need another table and convert the data to required formats on the way?
It's flat data (with variable lengths), so everything is possible here. But, we need just a bit more clarification... like how do you want the data to be split into columns? In the following, which is one of your lines of data, how do you want the columns to be split out from the data? I've marked my guess at it but I need your confirmation. I've number the fields in the line to make discussion easier.
APS06 C5_LAKE_WE85912_003_8591200305PB DON 03/03/201014:07:26 West_Cutter 10295Wed Mar 03 14:08:25 2010 30 24 0
|---| |------------------------------| |-| |----------------| |---------| |---||----------------------| || || ||
1 2 3 4 5 6 7 8 9 10
I also need to know what the final target table would look like. It would be of great help if you could provided a CREATE TABLE statement to receive the above data and well as a brief explanation as to which fields from the raw data will go into which fields in the receiving table.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2013 at 11:52 am
[FieldFixedLength(10)] Machine;
[FieldFixedLength(20)] Job;
[FieldFixedLength(10)] Mode;
[FieldFixedLength(10)] User;
[FieldFixedLength(20)] JobStart;
[FieldFixedLength(25)] Object;
[FieldFixedLength(5)] AlarmId;
[FieldFixedLength(31)] AlarmStart;
[FieldFixedLength(5)] MachineFault;
[FieldFixedLength(5)] OperFault;
[FieldFixedLength(5)] PiecesFed;
All Fixed length
Jay
August 10, 2013 at 11:53 am
USE [SLADB]
GO
/****** Object: Table [dbo].[AlarmDet] Script Date: 08/10/2013 18:52:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AlarmDet](
[Machine] [nchar](10) NULL,
[Job] [nchar](20) NULL,
[Mode] [nchar](10) NULL,
[User] [nchar](10) NULL,
[JobStart] [datetime] NULL,
[Object] [nchar](25) NULL,
[AlarmID] [int] NULL,
[AlarmStart] [nchar](31) NULL,
[MachineFault] [int] NULL,
[OperFault] [int] NULL,
[PiecesFed] [int] NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_AlarmDet] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
August 10, 2013 at 3:53 pm
The sample data you posted is not fixed length, but I will have to assume that there were accidents when you pasted into the web form.
Your format file should look something like this:
10.0
12
1 SQLCHAR 0 10 "" 1 Machine Latin1_General_CI_AS
2 SQLCHAR 0 20 "" 2 Job Latin1_General_CI_AS
...
11 SQLCHAR 0 5 "" 11 PiecesFed Latin1_General_CI_AS
12 SQLCHAR 0 0 "\r" 0 "" ""
The first line is the version number of the format-file format. 10.0 is SQL 2008.
12 is the number of fields. You have 11, and I've added one extra for end-of-line.
The following lines are description of the file fields. The first column is the field number. The second is the data type, which is always SQLCHAR since this is a text file. (Or SQLNCHAR for a Unicode file.) The third should always be 0 for a text file. The fourth column is the field length, and here you provide your field widths. (Note that you need to double if you have a Unicode file.) The fifth column is the terminator, which you have none but at end of line. (For a Unicode file it should be \r\0\0.)
The sixth column is the target column in the database table, starting on 1. 0 means don't import. The seventh is the column name, but this is only informational. The last is the collation, which matters if there is non-ASCII data in the file.
I see a problem, your dates are formatted as
03/03/201014:56:30 and
Wed Mar 03 14:47:03 2010
None of those format will convert to datetime, so you would need to import them as strings and then parse them. Quite messy.
For this reason, I think a lot easier solution is to go back to the C#. This article on my web site shows how you can stream a file to a table-valued parameter:
http://www.sommarskog.se/arrays-in-sql-2008.html.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply