convert text file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

i was not sure what forum to pot this on, so i figure this would be a good
start, even for redirection pusposes. Users should be able to upload excel
data into a table. then this table information should be exported to a .csv
file, to get something like:

67.108.115.178,199.169.192.157,66.109.83.6,66.109.83.7

where te different ip addresses are separated by a comma. i tried doing
this, but i am getting each of the IP addresses on different lines of a .txt
fle, like this:

212.138.47.11
135.196.231.255
65.217.232.2
129.33.119.25
68.143.150.50
65.218.126.98
162.58.82.244
161.215.18.51
201.147.134.225
209.208.169.98

does anyone know how i can avoid getting eah IP adress on a different line,
and instead get them on one line, etc, or separated by a comma instead of by
a line?

thanks in advance,
geebee
 
Is the data from Excel coming in on one line, or one line per IP?
How is it stored in your table?
 
the date from excel is sotred as one IP address pr row. the data in access
is stored one IP address per table row.
 
Hopefully, you can do some VBA coding. This is what it will take. You will
need to create a recordset that contains the IP addresses you want to put
together, then you will need to write that out to a text file, but use the
extension of csv. This will cause Excel to see each IP in a different cell
on the same row.

Dim dbf as Database
Dim rst as Recordset
Dim strIPList as String
Dim lngRecCount as Long

set dbf = CurrentDb
set rst = dbf.MyTableName
If rst.RecordCount = 0 Then
MsgBox "No Data to Process"
GoTo MyExit
Else
rst.MoveLast
rst.MoveFirst
End If

For lngRecCount = 0 To rst.RecordCount -1
strIPList = strIPList & rst![IpAddress] & ","
Next lngRecCount

strIPList = Left(strIPList, Len(strIPList) -1) ' Takes off the last comma

' Now write it to a text file
 
thanks. i am having one problem though: when I type

Dim dbf As Database

the "database" option does not how up. however, the "dataaccesspage" option
does show up.

how can i get "database" to show up?

thanks in advance,
-geebee

Klatuu said:
Hopefully, you can do some VBA coding. This is what it will take. You will
need to create a recordset that contains the IP addresses you want to put
together, then you will need to write that out to a text file, but use the
extension of csv. This will cause Excel to see each IP in a different cell
on the same row.

Dim dbf as Database
Dim rst as Recordset
Dim strIPList as String
Dim lngRecCount as Long

set dbf = CurrentDb
set rst = dbf.MyTableName
If rst.RecordCount = 0 Then
MsgBox "No Data to Process"
GoTo MyExit
Else
rst.MoveLast
rst.MoveFirst
End If

For lngRecCount = 0 To rst.RecordCount -1
strIPList = strIPList & rst![IpAddress] & ","
Next lngRecCount

strIPList = Left(strIPList, Len(strIPList) -1) ' Takes off the last comma

' Now write it to a text file

geebee said:
the date from excel is sotred as one IP address pr row. the data in access
is stored one IP address per table row.
Click to expand...
Click to expand...
 
Check your references. In the VB Editor, Tools-->References. You should have
a reference to the Access Object Library and the DAO library. It may be one
of those missing. I have not seen this problem before. What happens if you
just type it in?

geebee said:
thanks. i am having one problem though: when I type

Dim dbf As Database

the "database" option does not how up. however, the "dataaccesspage" option
does show up.

how can i get "database" to show up?

thanks in advance,
-geebee

Klatuu said:
Hopefully, you can do some VBA coding. This is what it will take. You will
need to create a recordset that contains the IP addresses you want to put
together, then you will need to write that out to a text file, but use the
extension of csv. This will cause Excel to see each IP in a different cell
on the same row.

Dim dbf as Database
Dim rst as Recordset
Dim strIPList as String
Dim lngRecCount as Long

set dbf = CurrentDb
set rst = dbf.MyTableName
If rst.RecordCount = 0 Then
MsgBox "No Data to Process"
GoTo MyExit
Else
rst.MoveLast
rst.MoveFirst
End If

For lngRecCount = 0 To rst.RecordCount -1
strIPList = strIPList & rst![IpAddress] & ","
Next lngRecCount

strIPList = Left(strIPList, Len(strIPList) -1) ' Takes off the last comma

' Now write it to a text file

geebee said:
the date from excel is sotred as one IP address pr row. the data in access
is stored one IP address per table row.


:

Is the data from Excel coming in on one line, or one line per IP?
How is it stored in your table?


:

hi,

i was not sure what forum to pot this on, so i figure this would be a good
start, even for redirection pusposes. Users should be able to upload excel
data into a table. then this table information should be exported to a .csv
file, to get something like:

67.108.115.178,199.169.192.157,66.109.83.6,66.109.83.7

where te different ip addresses are separated by a comma. i tried doing
this, but i am getting each of the IP addresses on different lines of a .txt
fle, like this:

212.138.47.11
135.196.231.255
65.217.232.2
129.33.119.25
68.143.150.50
65.218.126.98
162.58.82.244
161.215.18.51
201.147.134.225
209.208.169.98

does anyone know how i can avoid getting eah IP adress on a different line,
and instead get them on one line, etc, or separated by a comma instead of by
a line?

thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
thanks. i made sure te DAO library was enabled and that solved the problem
with "database" not showing up. now i have anotherproblem. the folling line
of te code:

set rst = dbf.MyTableName

results in a "method ordata member not found" error message. why is this?

thanks in advance,
-geebee


Klatuu said:
Check your references. In the VB Editor, Tools-->References. You should have
a reference to the Access Object Library and the DAO library. It may be one
of those missing. I have not seen this problem before. What happens if you
just type it in?

geebee said:
thanks. i am having one problem though: when I type

Dim dbf As Database

the "database" option does not how up. however, the "dataaccesspage" option
does show up.

how can i get "database" to show up?

thanks in advance,
-geebee

Klatuu said:
Hopefully, you can do some VBA coding. This is what it will take. You will
need to create a recordset that contains the IP addresses you want to put
together, then you will need to write that out to a text file, but use the
extension of csv. This will cause Excel to see each IP in a different cell
on the same row.

Dim dbf as Database
Dim rst as Recordset
Dim strIPList as String
Dim lngRecCount as Long

set dbf = CurrentDb
set rst = dbf.MyTableName
If rst.RecordCount = 0 Then
MsgBox "No Data to Process"
GoTo MyExit
Else
rst.MoveLast
rst.MoveFirst
End If

For lngRecCount = 0 To rst.RecordCount -1
strIPList = strIPList & rst![IpAddress] & ","
Next lngRecCount

strIPList = Left(strIPList, Len(strIPList) -1) ' Takes off the last comma

' Now write it to a text file

:

the date from excel is sotred as one IP address pr row. the data in access
is stored one IP address per table row.


:

Is the data from Excel coming in on one line, or one line per IP?
How is it stored in your table?


:

hi,

i was not sure what forum to pot this on, so i figure this would be a good
start, even for redirection pusposes. Users should be able to upload excel
data into a table. then this table information should be exported to a .csv
file, to get something like:

67.108.115.178,199.169.192.157,66.109.83.6,66.109.83.7

where te different ip addresses are separated by a comma. i tried doing
this, but i am getting each of the IP addresses on different lines of a .txt
fle, like this:

212.138.47.11
135.196.231.255
65.217.232.2
129.33.119.25
68.143.150.50
65.218.126.98
162.58.82.244
161.215.18.51
201.147.134.225
209.208.169.98

does anyone know how i can avoid getting eah IP adress on a different line,
and instead get them on one line, etc, or separated by a comma instead of by
a line?

thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
set rst = dbf.MyTableName

results in a "method ordata member not found" error message. why is this?
Click to expand...

Should be

Set rst = dbf.OpenRecordset("MyTableName")

using of course your own table's actual name.

John W. Vinson[MVP]
 
Another problem. Im sorry if i seem like a spoon-fed aby, but the following
line of code:

Set rst = dbf.OpenRecordset("sheet1")

Is causing a runtime error 13 error message.

why?

thanks in advance,
geebee
 
Another problem. Im sorry if i seem like a spoon-fed aby, but the following
line of code:

Set rst = dbf.OpenRecordset("sheet1")

Is causing a runtime error 13 error message.

why?
Click to expand...

Probably because there is no Table named sheet1 in your database.

Check the VBA help for OpenRecordset - you probably also need to
specify the recordset type, e.g.

dbf.OpenRecordset("sheet1", dbOpenSnapshot)

John W. Vinson[MVP]
 
Back
Top