Solved

structuring csv data

Posted on 2011-03-24
36
286 Views
Last Modified: 2012-06-27
HI all,
I have a csv file which i would like to structure in to some sort of table form..just showing you a row of the data as an example
NIF0,NIFTY,3/23/2011,3/31/2011,5500,38,64.35,33.5,61.9,62,61.9,485682,1349116.95,6169050,-1272500,5480.25.i would like these to appear under the following headings
ric,Symbol,Date,Expiry,Strike,Open,High,Low,Close,LTP,Settle Price,No. of contracts,Turnover,OI,Change in OI.Could you please tell me whether this is possible and how this can be done.
Thanks in advance,
Kris      
                                                                                          
0
Comment
Question by:kris_257
  • 16
  • 12
  • 2
  • +3
36 Comments
 
LVL 9

Expert Comment

by:jkofte
ID: 35205186
you can open the csv file in excel, add headers and "save as" an excel file.
0
 

Author Comment

by:kris_257
ID: 35205194
thats something i already know.the reason im not doing that is because  i want to query this csv via sql so i thought it would be better to structure the data and then query
0
 
LVL 9

Expert Comment

by:jkofte
ID: 35205199
then you can import the file to a table in sql server via right click >> tasks >> import data
0
 

Author Comment

by:kris_257
ID: 35205234
oh  ok..thanks for that..but is there anyway to structure the csv other than these..i  might be querying the csv from vba via an ADO.....so basically i want to write vba code to structure my data in that format
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205257
Yes. We can write a small macro or a vbscript but for that Can you give me a sample for "Before" and "After" sample in Excel File. The file can contain only two rows for testing purpose.

Sid
0
 

Author Comment

by:kris_257
ID: 35205290
hi sid,
attached the file for before..not quite sure how it should appear after the formatting..but  ideally the data should be classified under the following headings
ric,Symbol,Date,Expiry,Strike,Open,High,Low,Close,LTP,SettlePrice,No.ofcontracts,Turnover,OI,Change in OI.
every field should appear under its corresponding  heading something like a table which i can query via an ADO.hope this is what you were looking for
thanks,
kris

before.csv
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205311
Kris there are 15 column heads but the csv has 16 columns data. See how this looks in 'after'.

What would you suggest?

Sid
After.xls
0
 

Author Comment

by:kris_257
ID: 35205480
hi sid,
Sorry for the mistake ,the last column heading is underlying value...ya it looks perfect....but  i dont want it to be an xls..i want a csv file only...
Thanks,
kris
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205509
Sure not a problem. Let me work on it :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205519
Quick question.

You mentioned that you want a VBA code. My suggestion is that instead of using a VBA code, simply use vbscript. That ways you don't need Excel. If my understanding is correct then you just want to add headers to the before.csv. Am I correct?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205569
This is what I am referring to

Open Notepad and paste the code below. Then save the file as say "Format.Vbs". And you are done. Simply run the code by double clicking on the "Format.Vbs" file and the "before.csv" will be formatted to what you want.

Also do remember to change the path of "before.csv" in the code below.

Sid

Code

Const SourceFile = "C:\Before.Csv"
Const ForReading = 1
Const strHeaders = "Ric,Symbol,Date,Expiry,Strike,Open,High,Low,Close,LTP,Settle 

Price,No. of contracts,Turnover,OI,Change in OI,Underlying value"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(SourceFile, ForReading)

Dim arrFileLines()

i = 0

Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop
objFile.Close

objFSO.DeleteFile SourceFile,True

Set objFile = objFSO.CreateTextFile(SourceFile)
objFile.WriteLine(strHeaders)

For Each strLine in arrFileLines
objFile.WriteLine(strLine)
Next
objFile.Close

WScript.Echo "Done"

Open in new window

0
 

Author Comment

by:kris_257
ID: 35205572
yes vbscript should be perfect....I have very limited knowledge about vbscript though...
thanks for helping out
kris
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205589
Seems we both posted at the same time :)

See my last post above.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205595
In fact here is the text file. Simply open it and save it as "Format.Vbs"

Sid
Code.txt
0
 

Author Comment

by:kris_257
ID: 35205641
im getting a runtime error at line19 char1....cant figure out why....
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205643
Show me the exact code that you r using.

Sid
0
 

Author Comment

by:kris_257
ID: 35205653
Const SourceFile = "C:\Documents and Settings\Krishna.Sriram\Desktop\put.csv"
Const ForReading = 1
Const strHeaders = "Ric,Symbol,Date,Expiry,Strike,Open,High,Low,Close,LTP,Settle Price,No. of contracts,Turnover,OI,Change in OI,Underlying value"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(SourceFile, ForReading)

Dim arrFileLines()

i = 0

Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop
objFile.Close

objFSO.DeleteFile SourceFile,True( iguess this is whats throwing an error)

Set objFile = objFSO.CreateTextFile(SourceFile)
objFile.WriteLine(strHeaders)

For Each strLine in arrFileLines
objFile.WriteLine(strLine)
Next
objFile.Close

WScript.Echo "Done"
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205662
>>>>objFSO.DeleteFile SourceFile,True( iguess this is whats throwing an error)

Two reasons it can give you an error on that line

1) Is the file open?
2) Do you have Rights to delete file in that folder?

I have tested it on my pc and it is working just fine.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205721
Ok See this video. It works :)

Sid
SiddharthRout-436280.flv
0
 

Author Comment

by:kris_257
ID: 35205741
hey ,yes  it works...thanks a ton.....if possible could you also suggest the best approach to query from this file??
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205805
>>>hey ,yes  it works...thanks a ton.....if possible could you also suggest the best approach to query from this file??

Glad it works :)

Do you mean run the query from the vbscript?

Sid
0
 

Author Comment

by:kris_257
ID: 35205818
actually i have a whole lot of data in my csv,so you know i would be looking to write a query to retreive say a particular row of data....so what do you think would be the easiet and simplest approach to do so...?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205829
easiet and simplest approach would be again from vbscript as we already have the data in the arrFileLines() in the above code :)

All we need to do now is just retrieve the relevant data from the array.

Sid
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35205966
Create a table in Access - use your new field headings.
Import, or link, the CSV file to Access.
Append the linked CSV file to the Access table using an append query (save it for later use).
You can now query the new Access table (your field headings) from Access or Excel.
Scott C
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205985
Clark. I feel and correct me if I am wrong that it would be 'simple' to do it from he vbs as the data is already in the array?

BTW I like "Old man can rock " Simply Amazing!!!! :)

Sid
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35206172
I just threw this 'in the mix' as an alternate (or future) suggestion.

Thanks for the compliment!... that's what I do for real.  This computer stuff is what I do in-between..
 :-).  There's some live stuff I've done on my YouTube channel as well.
Have a great day!

Scott C
0
 

Author Comment

by:kris_257
ID: 35229615
sid,
so vbs would be the most optimal solution to this problem right?
0
 

Expert Comment

by:sharathtk
ID: 35229672
Hi kris,

You can use Java and Open CSV for parsing CSV files, it is a  parser library written in java. It's very simple to use it. Refer this URL http://opencsv.sourceforge.net/
0
 

Expert Comment

by:sharathtk
ID: 35229705
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35230451
>>>>so vbs would be the most optimal solution to this problem right?

Like I said, since you are already using vbs and you have the data in the array it would be easy to simply continue the code and extract data from the array. If you can tell me what exactly you want and then maybe I can give you an example?

Sid
0
 

Author Comment

by:kris_257
ID: 35230621
well sid,do you remember how my sheet looks after the code you sent me for formatting ?you had sent me a sample called 'after.xls' if im not wrong.In that sheet i would be using a combination of the Ric and date to uniquely identify a single entry(row) and retreive it.so basically my query would have as input these 2 fields and it would output  that particular row...
thanks,
kris
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35307180
kris, do you have that file as I don't :(

Sid
0
 

Author Comment

by:kris_257
ID: 35320903
hey sid..
just scroll on top theres this file you attached called after.xls just change it to .csv...thats the exact file i would be querying thanks...
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35405744
Sorry

Was kind of busy as I has shifting from one house to the other.

Paste this code in a notepad and save it as FindRecord.vbs :)

Please run this file after you have run the VBS file that I gave above.

In this code, I am looking for the record

RIC = "NIF05500C1.NS"
Date=  "3/23/2011"

You may amend it as applicable.

Const SourceFile = "C:\Before.Csv"
Const ForReading = 1

Dim strLine
Dim arrFileLines
Dim strRIC, strDate

strRIC = "NIF05500C1.NS"
strDate = "3/23/2011"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(SourceFile, ForReading)

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    arrFileLines = Split(strLine, ",",-1, 1)
    If arrFileLines(0) = strRIC And arrFileLines(2) = strDate Then
        WScript.Echo strLine
        Exit Do
    End If
Loop
objFile.Close

Open in new window


Sid
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35821691
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now