Create CSV Based On Cell Contents 1

[Secplus]

Morning Folks,

I've come up with a spreadsheet which extracts relevant data from one worksheet and puts it into a specific format and order in another. I'm then exporting the contents of the second worksheet as a CSV, ready for import into another system.

The problem is that Excel's "Save As" csv facility, outputs rows with blank cells not just the ones containing valid data. These blank rows appear as line of commas in the CSV which cause problems when imported into the other system.

Is there a way I can create a CSV whose contents are based purely on rows containing "real" data? E.g. If cell A1 is a number then output the row to the CSV?

 

[JerryKlmns]

Secplus,

The best would be to amend the code that puts the data to the new worksheet in order to include only valid data. If it is out of question, then on the new sheet add a validation column with a function that returns true, do a sort on that column (non valid at top) and start deleting non valid data rows till you find the first valid. Then delete the validation column and save as .CSV

 

[CautionMP]

Secplus,
I agree with [navy]JeryKlmns[/navy], the whole garbage in = garbage out principal applies.

I also exist in the real world so here is another approach that uses a macro to create the output file and checks for [tt]NULL[/tt] records.

[navy]Sub [/navy] SaveAsCleanCSV(FileName [navy]As String[/navy])
[navy]Dim[/navy] wksToOutput [navy]As[/navy] Worksheet
[navy]Dim[/navy] rngActive [navy]As[/navy] Range, rngRow [navy]As[/navy] Range, rngCell [navy]As[/navy] Range
[navy]Dim[/navy] intFile [navy]As Integer[/navy]
[navy]Dim[/navy] strRecord [navy]As String[/navy], strEmptyRecord [navy]As String[/navy]
[green]'Get workbook Objects[/green]
[navy]Set[/navy] wksToOutput = ActiveSheet
[navy]Set[/navy] rngActive = wksToOutput.UsedRange
[green]'Define an Empty record[/green]
strEmptyRecord = [navy]String[/navy](rngActive.Columns.Count, ",")
[green]'Get the output file stuff[/green]
intFile = FreeFile
Open FileName [navy]For[/navy] Output [navy]As[/navy] #intFile
[green]'Loop through the rows[/green]
[navy]For Each[/navy] rngRow In rngActive.Rows
  [green]'Loop through the cells (columns) of the current row[/green]
  [navy]For Each[/navy] rngCell In rngRow.Columns
    [green]'NOTE: This will create a dangling comma at the End of[/green]
    [green]'      Each line[/green]
    strRecord = strRecord & rngCell & ","
  [navy]Next[/navy] rngCell
  [green]'Test To make sure there is data[/green]
  [navy]If[/navy] strRecord <> strEmptyRecord [navy]Then[/navy]
    [green]'SEnd line To file[/green]
    Print #intFile, strRecord
  [navy]End If[/navy]
  [green]'ReSet For Next row[/green]
  strRecord = ""
[navy]Next[/navy] rngRow
Clean_Up:
Close intFile
[navy]Set[/navy] rngCell = [navy]Nothing[/navy]
[navy]Set[/navy] rngRow = [navy]Nothing[/navy]
[navy]Set[/navy] rngActive = [navy]Nothing[/navy]
[navy]End Sub [/navy]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JerryKlmns]

CautionMP,

I liked very much your empty record definition

[green]'Define an Empty record[/green]
strEmptyRecord = String(rngActive.Columns.Count, ",")

And maybe a star would be most suitable for your idea. So there goes the pinky..........