Convert and Combine some stupid CSV format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert and Combine some stupid CSV format

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-08-22 : 05:52:30

Dear Gurus,

I got a monthly report generated from Cisco Call Manager. It is in CSV file, full of crap, and the CSV is wrongly formatted.
Example:

Jun 1, 2011,1:40:20 AM,1:42:13 AM,92, 3919, 00194050053,G711Ulaw 64k,G711Ulaw 64k,SEP002414B26F1E,172.17.204.2,NA,NA

Notice the flaws above:

1. Text are not quoted.
2. Commas without empty space behind are supposed to be part of the text "Jun 1, 2011", but no way I can import by ignoring ", " and use only ",". Resulting the empty leading space after import and also "Jun 1, 2011" imported as "Jun 1" and " 2001"

3. Date are written in language form. "Jul 1, 2011" instead of 2011/07/01.
4. Time is written in h:mm:ss tt instead of proper HH:mm:ss

All the above issues resulting hell of my time trying to import it properly.

This is what I plan to do.
Step:
1. Import all crap into a temp table called "CSV"
2. Transfer from CSV Table to actual table while convert/combine all rubbish

I will sure face a lot of problems when I try to convert and trasfer this crappy table into a table with proper format.

This is what I do:

CREATE TABLE CSV
(
csvDate NVARCHAR(50),
csvYear NVARCHAR(50),
csvOriTime NVARCHAR(50),
csvTermTime NVARCHAR(50),
csvDuration NVARCHAR(50),
csvOrig NVARCHAR(50),
csvDest NVARCHAR(50),
csv1 NVARCHAR(50),
csv2 NVARCHAR(50),
csv3 NVARCHAR(50),
csv4 NVARCHAR(50),
csv5 NVARCHAR(50),
csv6 NVARCHAR(50)
)
GO

BULK
INSERT CSV
FROM 'c:\temp\201106.csv'
WITH
(
-- Would it be possible do something with the FIELDTERMINATOR?
-- FIELDTERMINATOR = ',' but not ', ' 

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT csvDate
FROM CSV
GO

My first questions as below,
1. Is it possible to use FIELDTERMINATOR=',' but not ', ' ?
2. Can I do selective Import from CSV? Example, I don't need the last 6 fields?
3. After I imported into SQL Server, how do I convert a text of "Jul 1" to "07/01"?
4. I how do I convert a text of "1:40:20 AM" to "01:40:30" (HH:mm:ss)?

More questions I might need to ask later...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 05:55:25

did you try using a format file and trying to export your required data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-08-22 : 06:26:41

so far I tried the date, it works OK as below:
SELECT
RIGHT(csvYear,4) + '/' +
CASE LEFT(csvDate, 3)
when N'Jan' then N'01'
when N'Feb' then N'02'
when N'Mar' then N'03'
when N'Apr' then N'04'
when N'May' then N'05'
when N'Jun' then N'06'
when N'Jul' then N'07'
when N'Aug' then N'08'
when N'Sep' then N'09'
when N'Oct' then N'10'
when N'Nov' then N'11'
when N'Dec' then N'12'
END
+ '/' + right('0' + ltrim(right(csvDate, 2)), 2)
AS csvDate

now i am trying to do similar, applying to AM and PM... but having difficulty trying to convert string to int back and forth...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 06:36:56

for converting date see the example below


SELECT CAST(REPLACE('Jun 1, 2011,1:40:20 AM',',','') AS Datetime)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-08-22 : 07:15:44

I am afraid the solution is not that simple. I need to convert the time from h:mm:ss tt to HH:mm:ss

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 07:23:45

but you've AM/PM following time part rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-08-24 : 12:10:20

After two days of messing the SQL Server, this is my solution, please comment


--Create a table for CSV (Created from Scripted Table)
CREATE TABLE [CSV](
	[csvDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csvYear] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csvOriTime] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csvTermTime] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csvDuration] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csvOrig] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csvDest] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csv1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csv2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csv3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csv4] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csv5] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[csv6] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

--Delete all records from CSV Table
--TRUNCATE TABLE CSV
--GO

--Import all records from CSV File to CSV Table
BULK
INSERT CSV
FROM 'c:\temp\201106.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

--Delete all records from Call Table
--TRUNCATE Table Phone_Calls
--GO

--Create Table for Phone_Calls (From Scripted table)
CREATE TABLE [dbo].[Phone_Calls](
	[CallDatetime] [datetime] NULL,
	[CallDuration] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CallFrom] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CallDestination] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


--Insert all relevant records from CSV Table to Call Table
INSERT INTO Phone_Calls (
CallDatetime, 
CallDuration, 
CallFrom, 
CallDestination)

SELECT

-- Add 6 hours to convert +2 Time Zone to +8 local time zone
Dateadd(hh, 6,
CAST(
-- Get the Month
CASE LEFT(csvDate, 3) 
  when N'Jan' then N'1'
  when N'Feb' then N'2'
  when N'Mar' then N'3'
  when N'Apr' then N'4'
  when N'May' then N'5'
  when N'Jun' then N'6'
  when N'Jul' then N'7'
  when N'Aug' then N'8'
  when N'Sep' then N'9'
  when N'Oct' then N'10'
  when N'Nov' then N'11'
  when N'Dec' then N'12'
END 

--Add Day
+ '/' + ltrim(right(csvDate,2))

--Add Year
+ '/' + RIGHT(csvYear,4)

-- Add Time
+ ' ' + 
csvOriTime

AS Datetime)
)
AS csvDatetime,

-- Add the call Duration in seconds
csvDuration,

-- Add the Caller Extension
ltrim(csvOrig),

-- Add the Call to destination
ltrim(csvDest)
FROM csv

where 
-- only the call is from an extension (some calls are automated)
csvOrig <> ' '

-- Calling external number must be more than 5 numbers, anything less than that are internal call
and len(csvDest) > 5
order by csvDateTime
Go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources