structuring csv data

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      
                                                                                          
kris_257Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
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
 
jkofteCommented:
you can open the csv file in excel, add headers and "save as" an excel file.
0
 
kris_257Author Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jkofteCommented:
then you can import the file to a table in sql server via right click >> tasks >> import data
0
 
kris_257Author Commented:
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
 
SiddharthRoutCommented:
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
 
kris_257Author Commented:
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
 
SiddharthRoutCommented:
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
 
kris_257Author Commented:
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
 
SiddharthRoutCommented:
Sure not a problem. Let me work on it :)

Sid
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
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
 
kris_257Author Commented:
yes vbscript should be perfect....I have very limited knowledge about vbscript though...
thanks for helping out
kris
0
 
SiddharthRoutCommented:
Seems we both posted at the same time :)

See my last post above.

Sid
0
 
SiddharthRoutCommented:
In fact here is the text file. Simply open it and save it as "Format.Vbs"

Sid
Code.txt
0
 
kris_257Author Commented:
im getting a runtime error at line19 char1....cant figure out why....
0
 
SiddharthRoutCommented:
Show me the exact code that you r using.

Sid
0
 
kris_257Author Commented:
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
 
SiddharthRoutCommented:
>>>>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
 
SiddharthRoutCommented:
Ok See this video. It works :)

Sid
SiddharthRout-436280.flv
0
 
kris_257Author Commented:
hey ,yes  it works...thanks a ton.....if possible could you also suggest the best approach to query from this file??
0
 
SiddharthRoutCommented:
>>>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
 
kris_257Author Commented:
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
 
SiddharthRoutCommented:
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
 
clarkscottCommented:
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
 
SiddharthRoutCommented:
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
 
clarkscottCommented:
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
 
kris_257Author Commented:
sid,
so vbs would be the most optimal solution to this problem right?
0
 
sharathtkCommented:
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
 
sharathtkCommented:
0
 
SiddharthRoutCommented:
>>>>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
 
kris_257Author Commented:
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
 
SiddharthRoutCommented:
kris, do you have that file as I don't :(

Sid
0
 
kris_257Author Commented:
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
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.