Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP Help


BCP Help

Author
Message
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 161
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?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45100 Visits: 39912
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

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 161
[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
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 161
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
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 161
The file is 600mb + that I am trying to get to the tables, could I import all as string then copy to another table and parse on way. ? Or do you still advise using code ?
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
jerome.morris (8/11/2013)
The file is 600mb + that I am trying to get to the tables, could I import all as string then copy to another table and parse on way. ? Or do you still advise using code ?


That's a common technique, but parsing before loading is more efficient. Then again, it depends on what fits in your overall situation.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 161
In the end I opted for converting the flat file to csv then bulk inserting into a temp table all Varchar(100). Now this works great from my C# app but I am at a stop again and google is not really helping. How would you advise I copy this data to the correct table and convert the format on the way ?

Temp Table

USE [SLADB]
GO

/****** Object: Table [dbo].[AlarmDetTest] Script Date: 08/21/2013 16:58:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AlarmDetTest](
[Machine] [varchar](100) NULL,
[Job] [varchar](100) NULL,
[Mode] [varchar](100) NULL,
[User] [varchar](100) NULL,
[JobStart] [varchar](100) NULL,
[Object] [varchar](100) NULL,
[AlarmID] [varchar](100) NULL,
[AlarmStart] [varchar](100) NULL,
[MachineFault] [varchar](100) NULL,
[OperFault] [varchar](100) NULL,
[PiecesFed] [varchar](100) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



Master Table

USE [SLADB]
GO

/****** Object: Table [dbo].[AlarmDet] Script Date: 08/21/2013 16:59:18 ******/
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

Thanks
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
jerome.morris (8/21/2013)
In the end I opted for converting the flat file to csv then bulk inserting into a temp table all Varchar(100). Now this works great from my C# app but I am at a stop again and google is not really helping. How would you advise I copy this data to the correct table and convert the format on the way ?


Answer: hard work.

For integer columns you need something like:

CASE WHEN col NOT LIKE '%[^0-9]%' THEN convert(int, col) END

For the date columns you will have to excel in an orgy of substring and other string functions.

Although, I still don't understand you don't you write a .Net program that reads the file and pass the data in a TVP. .Net has better string-handling abilities than T-SQL. Now you are first converting the file to CSV, load it into a staging table - and you are still not at the target.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 161
Because I don't have the skill set to do that yet :-(
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search