Output to Text File using VBA

[geoffcodd]

Hi there,

I have a table which is 23 Columns Wide, the data in Row 1 only contains data in the first 11 Columns.

I need to export this to a csv file but I can use the usually export features of Access as it adds 12 extra delimiters on the first line which causes the file to fail in the next process.

Does anyone have any ideas how I can accomplish this.

Thanks in advance
Geoff

 

[Me!User]

Filter the table with a query.

Create a query that selects the first eleven columns, from the 23-column-wide table, then export the query to a CSV file. Would that work for your situation?

 

[geoffcodd]

THis won;t work, as then all the remain rows will only have the first 11 columns exported and not all 23.

Thanks anyway
Geoff

 

[Me!User]

Two solutions:

Streamlined and clean but requires coding effort:
Building a custom export procedure that inspects the data and discards blank fields.

Fast and dirty:
Do something expedient, like manually erase the unwanted "","","" from the exported file using a text editor.

 

[Brian1960]

Web Version

You could adapt this to suit. dbConnect is the current database connection.

strSql1 = "SELECT * FROM TblName where Selectionfield = " & Selector

	'create recordset
	Set rsData = Server.CreateObject("ADODB.Recordset")
	rsData.Open strSQL1,dbConnect,adOpenDynamic,adLockPessimistic,adCmdText
			With rsdata
				if not .eof then
					For Each F In .Fields
					Head = Head & "," & F.Name
					Next
					Head = Mid(Head,2) & vbCrLf
					strcsv = .GetString(,,"~",vbCrLf," ")
					strcsv = Replace(strcsv,",",".")
					strcsv = Replace(strcsv,"~",",")
				End if
			End With
	rsdata.close
dbConnect.close
file_being_created= "Database/ShowAllData.csv"

' create a file system object
set fso = createobject("scripting.filesystemobject")

' create the text file - true will overwrite any previous files
' Writes the db output to a .xml file in the same directory 
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)
act.write (Head)
act.write(strCSV)
act.close

 

[Bat17]

What is the "Next Process"? using commas to mark the missing fields is standard practice for a CSV file.

Peter