1.

Solve : need script (*.cvs file items export to other files) [solved]?

Answer»

Hi there,

I have *.csv file with many lines of users - Name,Surname,Mobile,etc.
What I want to do is create/use some script witch would parse this *.csv file, take these values from here and put this into another file - each user line into separate file.
In my case what I want is to put user information into an *.HTML templated file.
User info goes like this:

...
(1st line) Name Surename
(2nd line) Mobile
(3rd line) etc
...


I will appreciate if someone could point me where to start from or give some advice to solve this.

Best Regards,
SushyMore questions than answers for now. Are these HTML templated files being created from scratch, or do they exist and you want to insert records? Do all the records in the csv file have have same number of data fields? How many?

Any of the script languages, including the underpowered batch command language, can be used to parse the csv file.

I notice that name and surname are two chunks of data in the csv file, but are formatted to one line in the html file. Is this the only exception or are there others?

You have elipses (...) within the html tags. What do these represent?

Quote from: Sidewinder on August 07, 2008, 05:09:00 AM

More questions than answers for now. Are these HTML templated files being created from scratch, or do they exist and you want to insert records? Do all the records in the csv file have have same number of data fields? How many?

HTML file(s) exist and I want to insert additional records from CSV file.
CVS file looks like this: (Name,Surname,position,phone,mobile,fax)
Code: [Select]Johny,Beier,marketing specialist,12345678,12345678,12345678
Larry,Cash,administrator,,12345678,12345678
...
...
HTML file looks like this:
Code: [Select]<html>
...
Best Regards,
!Johny Beier
!marketing specialist
!12345678
!12345678
!12345678
...
</html>
Values marked with "!" I need to obtain from CSV file.

Any of the script languages, including the underpowered batch command language, can be used to parse the csv file.

I notice that name and surname are two chunks of data in the csv file, but are formatted to one line in the html file. Yes.
Is this the only exception or are there others?
As you can see above there are also empty ",," entries in CSV, cos not all have fax number or phone number, etc. In fact this CSV file is generated from XLS file.

You have elipses (...) within the html tags. What do these represent?
Ohh, sorry for vagueness, it was wrote as comment thought.


Not as elegant as I hoped, but serviceable:

Code: [Select]Const ForReading = 1
Const ForWriting = 2

Set fso = CreateObject("Scripting.FileSystemObject")
Set fs = fso.OpenTextFile("c:\temp\data.csv", ForReading) 'user change

Set fldr = fso.GetFolder("c:\temp") 'user change
Set fc = fldr.Files

For Each fi In fc
If fso.GetExtensionName(fi) = "html" Then
Set f = fso.OpenTextFile(fi, ForReading)
strContent = f.ReadAll
f.Close

Set f = fso.OpenTextFile(fi, ForWriting)
strData = fs.ReadLine
arrData = Split(strData, ",")

loc = InStr(1, strContent, "Best Regards,")
loc = InStr(loc, strContent, vbCrLf)

f.WriteLine Mid(strContent, 1, loc)
For n = 0 To UBound(arrData)
If n = 0 Then
f.WriteLine arrData(n) & " " & arrData(n+1)
n = 1
Else
f.WriteLine arrData(n)
End if
Next
f.WriteLine Mid(strContent, loc + 2)
f.Close
End if
Next
fs.Close

I flagged the lines where you need to make changes for your environment. Made some assumptions: The templates are in a single directory with a html extension and missing data in the CSV file creates a blank line in the template.

This could have been done directly from the spreadsheet, but since you went to the effort of creating a CSV file, I used it.

The script overwrites the original template files. Can be modified to create a new output file.

Save script with a vbs extension and run from the command line as cscript scriptname.vbs

There is a shameful line of code where the array index inside a loop is tampered with. Keep this to yourself. If it gets out I may be banned from Don's Bar & Grill. Big thanks helping me, but script don`t work
When I run the script I get this:
Code: [Select]C:\temp\run.vbs(13, 3) Microsoft VBScript runtime error: Input past end of fileDid you test it or maybe I`m doing something wrong though I did as you said hmm...Quote
Did you test it

Yes, it was tested. All posted snippets are tested unless otherwise indicated. I cannot duplicate the error.

Quote
C:\temp\run.vbs(13, 3) Microsoft VBScript runtime error: Input past end of file

Error can be caused by a file being open in another process or the file is empty. Judging from the location in the code, most likely a template file. The solution was based on each template file containing:

Quote
<html>
...
Best Regards,
...
</html>

Post the code you are ACTUALLY using. You'd be surprised at how much gets lost in translation.

Yes. You was right. My template html file was empty. (my mistake)
Now script works.
In fact I`ve just realized that I need some changes to VB script, because my real html template file should look like this:
Code: [Select]<html>
...
Best Regards,
<b>"name surname from csv file"</b><br />
"position from csv file"<br />
phone:+777 "number from csv file"<br />
mobile:+777 "number from csv file"<br />
fax:+777 "number from csv file"<br />
...
</html>

As you can see name and surname for example should go exactly between tags, etc.
Is it possible? Can you help me with it?

And of course in the very perfect and comfortable way there is only one html template and before changes to it script creates the copy of it, then changes 1st one taking info from csv file line 1, then again creates 3rd copy from the 2nd, than again script changes 2nd one taking info from csv file line 2 and so on...
And each TIME html file is named by Name Surname values which it contains.
Of course there is no problem for me to copy many templates as I need and numerate them

Quote
In fact I`ve just realized that I need some changes to VB script, because my real html template file should look like this:

In the future, take a deep breath, think about what you need and then post your request. Posting code on the installment plan is not very efficient.

Code: [Select]Const ForReading = 1
Const ForWriting = 2

Set fso = CreateObject("Scripting.FileSystemObject")
Set fs = fso.OpenTextFile("c:\temp\data.csv", ForReading)
Set f = fso.OpenTextFile("c:\temp\template.html", ForReading)

strContent = f.ReadAll
loc = InStr(1, strContent, "Best Regards,")
loc = InStr(loc, strContent, vbCrLf)
f.Close

Do Until fs.AtEndOfStream
strData = fs.ReadLine
arrData = Split(strData, ",")
Set fo = fso.OpenTextFile("c:\temp\" & arrData(0) & " " & arrData(1) & ".html", ForWriting, True) ' user change

fo.WriteLine Mid(strContent, 1, loc)
For n = 0 To UBound(arrData)
Select Case n
Case 0
fo.WriteLine "<br>" & arrData(n) & " " & arrData(n+1) & "</b><br />"
n = 1
Case 2
fo.WriteLine arrdata(n) & "<br />"
Case 3
fo.WriteLine "phone:+777 " & arrdata(n) & "<br />"
Case 4
fo.WriteLine "mobile:+777 " & arrdata(n) & "<br />"
Case 5
fo.WriteLine "fax:+777 " & arrData(n) & "<br />"
Case Else
fo.WriteLine arrData(n)
End Select
Next
fo.WriteLine Mid(strContent, loc + 2)
fo.Close
Loop
fs.Close

Made some assumptions:

1. Template file is labeled template.html
2. CSV file is labeled data.csv
3. New templates are labeled firstname lastname taken from the CSV file
4. Uses a single template file (format in earlier post) as the basis of the new templates

You can change any of these values. Missing data in the CSV file produces a template line with the literal only.

Run as instructed in an earlier post.

Good luck. Quote
In the future, take a deep breath, think about what you need and then post your request

I will.

Thank you very very much. You really helped me.
The script is perfect - exactly what I was looking for.


Discussion

No Comment Found

Related InterviewSolutions