Solved

How to manipulate data using fso

Posted on 2014-10-13
3
100 Views
Last Modified: 2014-10-28
I need to transfer data from csv to a sql database. I am using fso, but I have a problem with the date format in the csv.
The string looks like dd month yy, data1, data2. E.g. 05 OCT 14, 27, 54
The database has the date format yyyy-mm-dd

My problem is that I don't know how to swap between date and year inside the string,
0
Comment
Question by:Everlas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Expert Comment

by:Steven Wells
ID: 40379269
I would read the data into a string, then use the data parse function of .net to combine into format you need.

you could also do string format (DD MM YY,HH, MM)

Can you post what you have so far?
0
 
LVL 34

Accepted Solution

by:
ste5an earned 500 total points
ID: 40379446
D'oh? fso? fso is short for FileSystemObject under most circumstances.. The date format of your database is irrelevant.
What SQL Server version?

I would load the CSV into a staging table consisting of NVARCHAR() columns and parse it it in T-SQL as

DECLARE @DateLiteral NVARCHAR(255) = '05 OCT 14';

SET LANGUAGE us_english;

SELECT TRY_CONVERT(DATE, @DateLiteral, 6);

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40379725
SSIS can handle this with a ForEach File Loop, where it processes all files regardless of the name.

Adding PowerShell zone, as I'm guessing this can be done with a PowerShell script.
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question