Asked  6 Months ago    Answers:  5   Viewed   21 times

Does anyone happen to know if there is a token I can add to my csv for a certain field so Excel doesn't try to convert it to a date?

I'm trying to write a .csv file from my application and one of the values happens to look enough like a date that Excel is automatically converting it from text to a date. I've tried putting all of my text fields (including the one that looks like a date) within double quotes, but that has no effect.

 Answers

94

I have found that putting an '=' before the double quotes will accomplish what you want. It forces the data to be text.

eg. ="2008-10-03",="more text"

EDIT (according to other posts): because of the Excel 2007 bug noted by Jeffiekins one should use the solution proposed by Andrew: "=""2008-10-03"""

Tuesday, June 1, 2021
 
Juriy
answered 6 Months ago
60
  <input type="text" name="Username" autocomplete="off">

You can also put this on the form tag. Note this does not work consistently in all browsers.

Saturday, July 3, 2021
 
ranhan
answered 5 Months ago
90

@Mubeen Shahid I wrote the code snippet below based on your original request so it will not now fully meet your new conditions. If it is of no use then no matter just discard it.

The code will read a .csv file and format it in a single column in sheet 1. For this example the .csv file is called "NosToCol.csv" and you must supply your own path where shown thus <>. Parameters can be changed within the code snippet to suit.

Sub ReadCSVFile()

Dim ws As Worksheet
Dim fName As String, Txt1 As String, tmpvar As String
Dim fRow As Long, lRow As Long, Rw As Long
Dim Col As Long, rec As Long
Dim wrng As Range, cl As Range
Dim ifnum As Integer
Dim rearr(), wrarr

Set ws = Sheets("Sheet1")
fName = "<<yourpath>>NosToCol.csv"
fRow = 2  'Row 2
Col = 1   'Col A
Txt1 = ""
ifnum = 1

    With ws
        lRow = .Cells(Rows.Count, Col).End(xlUp).Row

        'READ DATA FROM FILE
        Open fName For Input Access Read As #ifnum
        rec = 0
            Do While Not EOF(ifnum)
                Line Input #ifnum, tmpvar
                rec = rec + 1
                ReDim Preserve rearr(1 To rec)
                rearr(rec) = tmpvar
            Loop
            Close #ifnum

         'WRITE DATA TO RANGE
            For c = 1 To rec
                wrarr = Split(rearr(c), ",")
                Set wrng = .Range(.Cells(fRow, Col), .Cells(fRow + UBound(wrarr, 1), Col))
                '.Range(.Cells(fRow, Col), .Cells(fRow + UBound(wrarr, 1), Col)).Value = Application.Transpose(wrarr)
                wrng.Value = Application.Transpose(wrarr)
                c = c + 1
                    'MODIFY CELL COLOUR
                    For Each cl In wrng
                        If cl = 0 Then cl.Interior.Color = vbRed Else cl.Interior.Color = vbGreen
                    Next cl
            Next c
    End With
End Sub
Thursday, September 2, 2021
 
Shawson
answered 3 Months ago
55

Rename the frame control to frameColours then you can loop its checkboxes & build your string;

Dim chk as Control
Dim colors as string, delimiter as string

for Each chk In Me.frameColours.Controls
    if typeOf chk Is msforms.CheckBox then
        if (chk.Value) then
            colors = colors & delimiter & chk.Caption
            delimiter = ","
        end if
    end if
next

With colorsSheet
    .Cells(ActiveCell.Row, 1).Value = colors
End With
Tuesday, October 26, 2021
 
Labe
answered 1 Month ago
46

For import:
Try MySql's built-in csv import: LOAD DATA INFILE

LOAD DATA INFILE 'yourdata.csv' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'n';

For export:
Try the jQuery plugin: table2csv. It converts an HTML table into a CSV file. Use the argument, delivery:'value', to return the csv as a string.

Here's an example implementation. I know it's in C#, but there's only a little code involved. It would be easy to port to PHP.

Tuesday, November 23, 2021
 
Nicola Pesavento
answered 7 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share