Formatting a workbook for database import

  • Thread starter Thread starter tehwa
  • Start date Start date
T

tehwa

Hello, and Merry Christmas

I have an output file in .xls format from a program, and need to impor
the data into a database. To do this I need to do some cleve
formatting to the worksheet via a macro.

I believe it to be possible, and have some rough pseudocode tha
describes the macros routine, however I have little to no knowledge o
the VB language and this makes things pretty difficult with a deadlin
that potentially erases my Christmas break.

I do not expect someone to relieve me of the task, but I require som
heavy assistance with the syntax to be used.

Firstly I need to select the first column's data:

<code>
Columns("A:A").Select
</code>

I suspect this to be correct syntax.

And find whether the first cell contains data.

If it does contains data, put that data into a variable:

<code>
If [cell].IsNotNull Then
[cell] = variable_referenceData

Then check the next cell in the column to see if it is null. If it i
null, insert the variable (the NotNull cell) into that cell, and d
this until it reaches a cell that is not empty. When it reaches a cel
in the column that is not empty, make this the ne
variable_referenceData:

if nextCell = null Then
[cell] = variable_referenceCell
Else nextCell = varaiable_referneceData;

Continue this until the end of the dataset (I am not sure if Excel wil
automatically do this, or whether I need to check when an empty cell i
reached, whether or not the whole row is null).

What I hope for this to do is to format a document that has th
following formatting (where ':' delimits cell seperations):

Name:detail
:detail
:detail
:detail

To a format of

Name:detail
Name:detail
Name:detail
Name:detial

The final piece of macro will save the file as a .csv file. I hope tha
someone can tell me if this is possible, and the syntax that will mak
it work.

Alternatively, if anyone has had this problem before (needing to impor
a csv file but has this formatting problem) I would dearly love to hea
of your solution. The program that produces the .xls file also export
as .csv, however it TABS the data without options to remove thi
formatting.

Thanks very much for your time, and Merry Christmas

.tehw
 
tehwa,

Let me know if this does something close to what you wanted.

The macro uses "data" for the name of the sheet and puts the original and
converted folder and file names into constants at the top of the macro. It
assumes your data is in the first two columns, with the first "Name" in A1,
although it will still work if it's farther down. Note that saving in csv
format drops any empty rows at the top of the sheet and puts the name of the
xls source workbook on the sheet tab.

Shockley


Private Const OrigFolder = "C:\Documents and
Settings\shockley\Desktop\Original\"
Private Const ConvFolder = "C:\Documents and
Settings\shockley\Desktop\Converted\"
Private Const OrigFileName = "DailyData.xls"
Private Const ConvFileName = "DailyData.csv"
Sub Tester()
Set wb = Workbooks.Open(OrigFolder & OrigFileName)
With wb.Sheets("data")
y = .Cells(.Rows.Count, 1).End(xlUp).Row
y1 = .Cells(.Rows.Count, 2).End(xlUp).Row

If y1 > y Then y = y1

OldName = .Cells(1, 1)
For i = 2 To y
sName = .Cells(i, 1)
If sName = Empty Then
If .Cells(i, 2) <> Empty Then _
.Cells(i, 1) = OldName
Else: OldName = sName
End If
Next i
End With

Application.DisplayAlerts = False
'Allows overwrite without prompt
wb.SaveAs _
FileName:=ConvFolder & ConvFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False
wb.Close
Application.DisplayAlerts = True

Set wb = Nothing
End Sub






tehwa said:
Hello, and Merry Christmas

I have an output file in .xls format from a program, and need to import
the data into a database. To do this I need to do some clever
formatting to the worksheet via a macro.

I believe it to be possible, and have some rough pseudocode that
describes the macros routine, however I have little to no knowledge of
the VB language and this makes things pretty difficult with a deadline
that potentially erases my Christmas break.

I do not expect someone to relieve me of the task, but I require some
heavy assistance with the syntax to be used.

Firstly I need to select the first column's data:

<code>
Columns("A:A").Select
</code>

I suspect this to be correct syntax.

And find whether the first cell contains data.

If it does contains data, put that data into a variable:

<code>
If [cell].IsNotNull Then
[cell] = variable_referenceData

Then check the next cell in the column to see if it is null. If it is
null, insert the variable (the NotNull cell) into that cell, and do
this until it reaches a cell that is not empty. When it reaches a cell
in the column that is not empty, make this the new
variable_referenceData:

if nextCell = null Then
[cell] = variable_referenceCell
Else nextCell = varaiable_referneceData;

Continue this until the end of the dataset (I am not sure if Excel will
automatically do this, or whether I need to check when an empty cell is
reached, whether or not the whole row is null).

What I hope for this to do is to format a document that has the
following formatting (where ':' delimits cell seperations):

Name:detail
:detail
:detail
:detail

To a format of

Name:detail
Name:detail
Name:detail
Name:detial

The final piece of macro will save the file as a .csv file. I hope that
someone can tell me if this is possible, and the syntax that will make
it work.

Alternatively, if anyone has had this problem before (needing to import
a csv file but has this formatting problem) I would dearly love to hear
of your solution. The program that produces the .xls file also exports
as .csv, however it TABS the data without options to remove this
formatting.

Thanks very much for your time, and Merry Christmas

tehwa
Click to expand...
 
Thanks a lot for the reply Shockley,

The name:detail was just an example, there are 6 columns, with varying
details. I will try to clarify my example a little:

*Name01*:detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04
*Name02*:detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04
*Name03*::detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04
[blank]:detail01:detail:02:detail03:detail04

..etc

(There are only 5 columns presented here for the sixth was wrapping)

So I need to copy "Name01" down until it reaches "Name02" then copy
that down until it hits "Name03" and so on. I am trying to find a way
to look at the next cell, and if it is null, populate it with the
previous cells data. Else, copy the data and look at the next cell.

The syntax is very interesting though, since I have been trying to find
a cell++ like count that I can loop (now I just need to find the loop
syntax for VB).

I am wondering Shockley, can I modify this code to make it look at the
next cell and check for null like this, then make it the previous cells
data. Else copy it and repeat the process until the end?

Thanks again fella.

tehwa
 
Tha'ts OK, I just modded and it worked:

y = .Cells(.Rows.Count, 1).End(xlUp).Row
y1 = .Cells(.Rows.Count, 2).End(xlUp).Row
y2...y5

works a treat, and does what I needed it to wonderfully. Thanks a lot
Shockley, I may see Christmas after all :).

..tehwa
 
tehwa,

Glad it worked--I figured you'd be able to modify to suit... Syntax is a
struggle!

Happy Holiday!
Shockley
 
Back
Top