ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Aplication to import and Convert Data (https://www.excelbanter.com/excel-programming/358480-aplication-import-convert-data.html)

Chris
Aplication to import and Convert Data
 
I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Tom Ogilvy
Aplication to import and Convert Data
 
You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Chris
Aplication to import and Convert Data
 
Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

"Tom Ogilvy" wrote:

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Tom Ogilvy
Aplication to import and Convert Data
 
I would guess that you didn't copy all the code since it does include an End
Sub statement in what I posted. Also, I tested it and the code was copied
directly from the module where it was working exactly as expected.

--
Regards,
Tom Ogilvy


"Chris" wrote:

Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

"Tom Ogilvy" wrote:

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Chris
Aplication to import and Convert Data
 
Tom,

I am getting another error (a debug error)

the following line is highlighted

" dt = CDate(m & "/" & d & "/" & y)"

Can I send you the file I have and maybe you can fixed it for us.

Please I really need your help
"Tom Ogilvy" wrote:

I would guess that you didn't copy all the code since it does include an End
Sub statement in what I posted. Also, I tested it and the code was copied
directly from the module where it was working exactly as expected.

--
Regards,
Tom Ogilvy


"Chris" wrote:

Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

"Tom Ogilvy" wrote:

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Tom Ogilvy
Aplication to import and Convert Data
 
Chris, you can send it. Make sure you include an email I can send it back
to.


--
Regards,
Tom Ogilvy

"Chris" wrote in message
...
Tom,

I am getting another error (a debug error)

the following line is highlighted

" dt = CDate(m & "/" & d & "/" & y)"

Can I send you the file I have and maybe you can fixed it for us.

Please I really need your help
"Tom Ogilvy" wrote:

I would guess that you didn't copy all the code since it does include an
End
Sub statement in what I posted. Also, I tested it and the code was
copied
directly from the module where it was working exactly as expected.

--
Regards,
Tom Ogilvy


"Chris" wrote:

Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

"Tom Ogilvy" wrote:

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the
tast at hand
I have a .csv file I need to import once that file is imported I
need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64


All times are GMT +1. The time now is 12:22 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com