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 ««123»»

BCP Help Expand / Collapse
Author
Message
Posted Saturday, August 10, 2013 4:34 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
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?
Post #1483018
Posted Saturday, August 10, 2013 11:44 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
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."

(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 #1483040
Posted Saturday, August 10, 2013 11:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
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
Post #1483042
Posted Saturday, August 10, 2013 11:53 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
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

Post #1483043
Posted Saturday, August 10, 2013 3:53 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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
Post #1483062
Posted Sunday, August 11, 2013 6:50 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
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 ?
Post #1483125
Posted Monday, August 12, 2013 1:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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
Post #1483159
Posted Wednesday, August 21, 2013 9:59 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
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

Post #1486788
Posted Wednesday, August 21, 2013 3:09 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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
Post #1486965
Posted Wednesday, August 21, 2013 3:15 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
Because I don't have the skill set to do that yet :-(

Post #1486968
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse