?
Solved

inserting column headers and a carriage return in a outfile to csv table

Posted on 2010-01-05
3
Medium Priority
?
346 Views
Last Modified: 2012-06-22
Greetings Experts;

I am probably killing a knat with a hammer but here goes. I have to create a csv file that will contain the results of a large form for up to 640 fields. I am doing a step in the process that is replacing an Excel file filled out by our clients with a Web enabled offering and then is imported into our solution.  The form is created and works well enough. The next step is to create a csv file that our techs can use to rename as a xls file and then use the import tool with our solution. The problem is I need create column headers for 8 columns then create a new row for each corresponding excel row.  I have only worked with the first two rows and will add the other 78 upon successfull write to file.  The content writes, but only to the first row. I need to add header names and loop to the next row after "default_entry_form_name_(x).

Thanks for any assistance on this.

Dave
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%> 
<!--#include file="trainingdb.asp" -->
<%
Dim rs_results
 
 
Set rs_results = Server.CreateObject("ADODB.Recordset")
rs_results.ActiveConnection = MM_trainingdb_STRING
rs_results.Source = "select * from cia_cte_expense_types_p1 where company_key = " & session("company")	
rs_results.CursorType = 0
rs_results.CursorLocation = 2
rs_results.LockType = 1
rs_results.Open()
 
rs_results_numRows = 0
%>
<%
account = Session("company")
'This script generates the downloadable data
DIM OutFileName,FSO,outFile,datastr,SQL,rs_outfile_app
 
OutFileName = "D:\Websites\exp_dynamic\" & account & "_expense_types.csv"
LogFileName = "D:\Websites\exp_dynamic\FileCreationLog_1.txt"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objLog = FSO.CreateTextFile(LogFileName, True)
On Error Resume Next
If FSO.FileExists(OutFileName) = True Then
	FSO.DeleteFile OutFileName
	If Err.Number <> 0 Then
		objLog.WriteLine "Error deleting " & OutFileName & VbCrLf & "Error " & Err.Number & ": " & Err.Description
	Else
		objLog.WriteLine "Successfully deleted " & OutFileName
	End If
Else
	objLog.WriteLine OutFileName & " does not already exist."
End If
Err.Clear
Set outFile = FSO.CreateTextFile(OutFileName, True)
If Err.Number <> 0 Then
	objLog.WriteLine "Error creating " & OutFileName & VbCrLf & "Error " & Err.Number & ": " & Err.Description
Else
	objLog.WriteLine "Successfully created " & OutFileName & ". Writing data..."
 
	outFile.writeline("active_exp_type_1,def_parent_exp_type_name_1,pref_parent_exp_type_name_1,default_exp_type_name_1,preferred_exp_type_name_1,account_code_1,billable_account_code_1,default_entry_form_name_1,active_exp_type_2,def_parent_exp_type_name_2,pref_parent_exp_type_name_2,default_exp_type_name_2,preferred_exp_type_name_2,account_code_2,billable_account_code_2,default_entry_form_name_2")
	datastr =  rs_results("active_exp_type_1") & "," _
		& rs_results("def_parent_exp_type_name_1") & "," _
		& rs_results("pref_parent_exp_type_name_1") & "," _
		& rs_results("default_exp_type_name_1") & "," _
		& rs_results("preferred_exp_type_name_1") & "," _
		& rs_results("account_code_1") & "," _
		& rs_results("billable_account_code_1") & "," _
		& rs_results("default_entry_form_name_1") & "," _
		& rs_results("def_parent_exp_type_name_2") & "," _
		& rs_results("pref_parent_exp_type_name_2") & "," _
		& rs_results("default_exp_type_name_2") & "," _
		& rs_results("preferred_exp_type_name_2") & "," _
		& rs_results("account_code_2") & "," _
		& rs_results("billable_account_code_2") & "," _
		& rs_results("default_entry_form_name_2")
	outFile.writeline(datastr) 
	outFile.Close
	objLog.WriteLine "Finished file output."
End If
Set outFile = Nothing
objLog.Close
Set objLog = Nothing
Set FSO = Nothing

%>
 
<%
rs_results.Close()
Set rs_results = Nothing
%>

<%
sdate_time_your_info = FormatDatetime(date,VBShortDate) & " " & FormatDatetime(time,vbShorttime)

%>
<body>

<p align="left"><font size="5" face="Arial, Helvetica, sans-serif">Download Expense Types</font></p>
<font size="2">
<p><font size="2" face="Geneva, Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><a href="http://expense.gelcotraining.com/dynamic/<%=account%>_expense_types.csv?query=<%=sdate_time_your_info%>">Download 
Expense Types Results</a></font></font></p>
</font>
<p><font size="2" face="Geneva, Arial, Helvetica, sans-serif"><font face="Arial, Helvetica, sans-serif"><a href="http://expense.gelcotraining.com/dynamic/FileCreationLog_1.txt">Download Log  Results</a></font></font><font size="2"><font face="Arial, Helvetica, sans-serif">
  </font></p> 

<p>&nbsp;</p>
<font size="2" face="Geneva, Arial, Helvetica, sans-serif">
<P><font face="Geneva, Arial, Helvetica, sans-serif"><em>&nbsp; </em></font> </P>
<P><em><font size="2">&nbsp;</font> </em> 
</font>
<p>

Open in new window

Copy-of-Expense-Types---National.htm
current-output-example.csv
0
Comment
Question by:Davidjevans
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26186573
Replace this:

        outFile.writeline("active_exp_type_1,def_parent_exp_type_name_1,pref_parent_exp_type_name_1,default_exp_type_name_1,preferred_exp_type_name_1,account_code_1,billable_account_code_1,default_entry_form_name_1,active_exp_type_2,def_parent_exp_type_name_2,pref_parent_exp_type_name_2,default_exp_type_name_2,preferred_exp_type_name_2,account_code_2,billable_account_code_2,default_entry_form_name_2")
        datastr =  rs_results("active_exp_type_1") & "," _
                & rs_results("def_parent_exp_type_name_1") & "," _
                & rs_results("pref_parent_exp_type_name_1") & "," _
                & rs_results("default_exp_type_name_1") & "," _
                & rs_results("preferred_exp_type_name_1") & "," _
                & rs_results("account_code_1") & "," _
                & rs_results("billable_account_code_1") & "," _
                & rs_results("default_entry_form_name_1") & "," _
                & rs_results("def_parent_exp_type_name_2") & "," _
                & rs_results("pref_parent_exp_type_name_2") & "," _
                & rs_results("default_exp_type_name_2") & "," _
                & rs_results("preferred_exp_type_name_2") & "," _
                & rs_results("account_code_2") & "," _
                & rs_results("billable_account_code_2") & "," _
                & rs_results("default_entry_form_name_2")
        outFile.writeline(datastr)  


With this:
Dim fld
datastr = ""
For Each fld in rs_results.Fields
      datastr = datastr & fld.Name & ","
Next
outFile.writeline Left(datastr, Len(datastr) - 1)

Do While Not rs_results.EOF
      datastr = ""
      For Each fld in rs_results.Fields
            datastr = datastr & fld.Value & ","
      Next
      outFile.writeline Left(datastr, Len(datastr) - 1)
      rs_results.MoveNext
Loop

I will leave the embedded commas as an excercise For you.
0
 

Author Comment

by:Davidjevans
ID: 26192626
Acperkins;
   
    Thanks very much for the code snippet. I used it and it indeed does output all 640 fields.  These issues still remain however. The first issue is that the outfile needs to insert a new row into the csv file after each "default_entry_form_name_(1-79)" field.  Without the new row, the file row quickly runs to the end of the csv file in Excell and stops at the 256th.  record (max field/row for Excell). I also need to include headers in the first row for each of the header types:

ACTIVATE; active_exp_type_1,active_exp_type_2,etc..
DEF_PRT_EXPENSE_TYPE_NAME: def_parent_exp_type_name_1, def_parent_exp_type_name_2, etc..
PRT_EXPENSE_TYPE_NAME: pref_parent_exp_type_name_1, pref_parent_exp_type_name_2, etc...
DEF_EXPENSE_TYPE_NAME: default_exp_type_name_1, default_exp_type_name_2, etc....
EXPENSE_TYPE_NAME: preferred_exp_type_name_1, preferred_exp_type_name_2, etc...
LEDGER: account_code_1, account_code_2 etc...
ENTRY_FORM: default_entry_form_name_1, default_entry_form_name_2 etc...

Regards

Dave
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 26196576
Try something like this:

Const OFFSET = 8
rs_results.CursorType = 2

...

Dim fld, FldNum, F
datastr = ""
FldNum = 1
For Each fld in rs_results.Fields
      datastr = datastr & fld.Name & ","
      If FldNum Mod OFFSET = 0 Then
            rs_results.MoveFirst                  ' This requires CursorType = adOpenDynamic
          outFile.writeline Left(datastr, Len(datastr) - 1)
            Do While Not rs_results.EOF
                  datastr = ""
                  For F = FldNum - OFFSET To FldNum - 1
                      datastr = datastr & fld(F).Value & ","
                  Next
                  outFile.writeline Left(datastr, Len(datastr) - 1)
                  rs_results.MoveNext
            Loop
            datastr = ""
      End If
      FldNum = FldNum + 1
Next
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

607 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