Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CSTR function not working

Status
Not open for further replies.
Trudye

Trudye

Programmer
Sep 4, 2001
932
US
I have a User that receives a *.CSV file daily. When the file is opened (in Excel) some of the values become invalid. There are alpha values in a numeric field (ie. 0907C). I wrote the code below to change all the values in that column to string/text but it is not working. The original format was general for that field.


Code: 
<html>
Sub Main_Processing()
Dim LastRow As Integer
Dim r As Variant
Dim n As Variant
Dim IntStr As String

'Pull in latest Nissan Detailed Grounding Report (File)
'Insure all Buyer_NNA & Grounding_Dealer_NNA entries are Text, this will reduce errors during
'Access Import

On Error GoTo MP_Error

    ChDir "C:\"
    Workbooks.Open Filename:="C:\RPMTbls\Nissan Detailed .xls"
                
'MyString = CStr(MyDouble)

Range("A1:A1").Select

'Determine last/Total Row - If there are empty cells in row D this will not work!!!!!!!!!
    LastRow = ActiveSheet.UsedRange.Rows.Count
    r = LastRow
    n = 16
    If Cells(1, "C") <> "GROUNDING_DEALER_NNA" Then
      Exit Sub
    End If

    If Cells(1, "P") <> "BUYER_NNA" Then
          exit sub
            End If

     For r = 2 To LastRow Step 1
        IntStr = CStr(Cells(r, "P"))
        Cells(r, n) = IntStr
    Next r
    
Application.Quit

MP_Error:
    If Err.Number = 1004 Or Err.Number = 440 Then
        MsgBox Err.Number & " - " & Err.Description
        MsgBox "No File exist with todays date, Excel file converstion will be bypassed"
        Exit Sub
    End If
        MsgBox Err.Number & " - " & Err.Description
End Sub
</html>
 
Sort by date Sort by votes
Trudye,

Perhaps I am misinterpreting your post, but it sounds as if you are trying to convert values in a numeric field to strings.

"a numeric field (ie. 0907C). I wrote the code below to change all the values in that column to string/text"

Also, could you be a bit more specific about the response you are getting. How do you know it isn't working?

Thanks
Bill
 
Upvote 0 Downvote


Thanks Bill for responding. When I attempt to import the file into Access I am getting errors on the field. The records that are showing up in the Error file are the ones that have alpha characters (ie. 0907C) and the value is left justified, all the other values in that column (for the recs that were accepted) are right justified.

I hope that helps.

 
Upvote 0 Downvote
Trudye,
Are you using [tt]TransferText()[/tt] to import the data, and if so are you using an Import Specification?

An Import Specification will let you tell Access (instead of Access guessing) what data type is in each field and should eliminate the conversion problem.

Hope this helps,
CMP



(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote

Thanks CM for responding. The file comes in as CSV and I open/save it as Excel (XLS). The problem is that most of the data in the field is numeric, there is the occasional string/text value.

When I import the file as CSV to create a spec for it I assign the field as Text(in the Advanced area). When the Import is complete I get the obligatory 'errors while importing' error msg. The recs that show up in the error file are the recs that I describe above (0907C). I have tried creating specs several ways and nothing works.

Thus the reason for the last ditch effort to use Excel to clean up the offending fields.
 
Upvote 0 Downvote
What about something like this in your code:
For r = 2 To LastRow Step 1
IntStr = CStr(Cells(r, "P"))
Cells(r, n) = [tt][!]"'" & [/!][/tt]IntStr
Next r


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote


Hi,

By chance, is this data coming from a MAINFRAME?

If so, this may be Something called Packed Decimal. The Alpha Characters are {, }, A thru I, J thru R. They mean something.

Skip,
[sub]
[glasses] [red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [tongue][/sub]
 
Upvote 0 Downvote
Trudye,
Another direction may be to transform the file before you Import, Link, or open in Excel. The following routine will open the file, wrap the first three fields in double quotes, and output the changed values as a new file.
Code: 
Sub TransformCSVFile()
Dim intFileIn As Integer, intFileOut As Integer, intField As Integer
Dim strRecord() As String, strLine As String
Dim strFileIn As String, strFileOut As String

'Open the input file
strFileIn = "C:\FileIn.csv"
intFileIn = FreeFile
Open strFileIn For Input As #intFileIn

'Open the output file
strFileOut = "C:\FileOut.csv"
intFileOut = FreeFile
Open strFileOut For Output As #intFileOut

'Process the files
Do
  Line Input #intFileIn, strLine
  strRecord() = Split(strLine, ",")
  For intField = 0 To UBound(strRecord)
    'This will wrap the selected fields in double quotes
    'field number is zero based
    Select Case intField
      Case 0, 1, 2
        strRecord(intField) = Chr(34) & strRecord(intField) & Chr(34)
      Case Else
        'Do nothing, field is ok
    End Select
  Next intField
  strLine = Join(strRecord, ",")
  Print #intFileOut, strLine
Loop Until EOF(intFileIn)

Close #intFileIn
Close #intFileOut
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote
Thanks to everyone for taking the time to respond.

PHV I tried you suggestion and nothing changed.

Skip - No the field is an ID number and the character is just character. But that is curious that you should ask, I asked if it was packed decimal too.

CMP - I have not had an opportunity to take a close look at your response. I'm not even sure I understand it. LOL

Thanks much,
Trudye

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Code not working 1
Replies
5
Views
149
Darrylles
Darrylles
Moss100
  • Locked
  • Question
Vba filter not working 3
Replies
7
Views
219
Cole Mendoza
Cole Mendoza
theConjurian
  • Locked
  • Question
DoCmd.Close not working
Replies
1
Views
117
kjv1611
kjv1611
Moss100
  • Locked
  • Question
Stop user changing size of Access Application (not form)
Replies
1
Views
390
Andrzejek
Andrzejek
JayRo71
  • Locked
  • Question
docmd.TransferText not importing a number field
Replies
1
Views
206
JayRo71
JayRo71

Part and Inventory Search

Sponsor

Back
Top