Undefined function 'REPLICATE' in expression

[Cameronsmith63]

Error that I get:
ERROR [42000] [Microsoft][ODBC Text Driver] Undefined function 'REPLICATE' in expression.

Code below:

DataTable dt = new DataTable();
            string tempPath = @"C:\swd\";
            string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @";Extensions=csv,txt";
            string SqlString = "Select  RIGHT(REPLICATE('0', 10) + RowNr, 10)," 
            + "RIGHT(REPLICATE('0', 10) + last_batch_number, 10) from Book1.csv";

            OdbcConnection conn = new OdbcConnection(strConn);
            OdbcDataAdapter da = new OdbcDataAdapter(SqlString, conn);

            conn.Open();
            da.Fill(dt);
            conn.Close();

Thanks

[pritaeas]

Try REPEAT('0', 10)

[Cameronsmith63]

ERROR [42000] [Microsoft][ODBC Text Driver] Undefined function 'REPEAT' in expression.

[Cameronsmith63]

I even tried this:

string SqlString = "SELECT REPLACE(STR(RowNr, 10), SPACE(1), '0') FROM Book1.csv";

            OdbcConnection conn = new OdbcConnection(strConn);
            OdbcDataAdapter da = new OdbcDataAdapter(SqlString, conn);

            conn.Open();
            da.Fill(dt);

and receive this error:

ERROR [42000] [Microsoft][ODBC Text Driver] Wrong number of arguments used with function in query expression 'REPLACE(STR(RowNr, 10), SPACE(1), '0')'.

[Ketsuekiame]

You could try installing the VB6 runtime. It's a long shot but it might be a corrupt/missing library issue: Click Here

[Cameronsmith63]

Still not working. it runs perfectly through MS SQL Server 2012 though.

[Cameronsmith63]

Any ideas on how to pad my values? the numbers in the 2 columns of my data table have a variable length, it must be at max 10 digits long and left padded with 10 digits.

[Cameronsmith63]

 I am trying my hand at this:

using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
            {
                DataTable tbl = new DataTable("MyTable");
                adp.Fill(tbl);

                foreach (DataRow row in tbl.Rows)
                {

                  //  row[0] = row[0].ToString().PadLeft(10,'0');
                }
            }
        }

[Cameronsmith63]

ok I got this part right:

using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
            {
                DataTable tbl = new DataTable("MyTable");
                adp.Fill(tbl);

                foreach (DataRow row in tbl.Rows)
                {

                  //  row[0] = row[0].ToString().PadLeft(10,'0');
                    row[0] = int.Parse(row[0].ToString().PadLeft(6, '0'));
                    row[1] = int.Parse(row[1].ToString().PadLeft(6, '0'));
                }
            }

I tested it with padright and it displays values as 100000, 400000, 700000.

But I need to left pad it as in the code, and the problem with that is that the result is 1, 4, 7 where I really need it to be 000001, 000004, 000007.

[Ketsuekiame]

Your padding is unecessary as you are parsing it to an int. 000001 simply becomes 1 once parsed. If you need to retain the leading 0s then you need to store the data as a string, not a number.

Also, you could use String.Format("{0:000000}", row[0]); as it looks a little neater, but this is personal preference. Using PadLeft is generally more correct as the intention behind the code is clearer. This won't fix the problem though, just another way of doing what you're currently doing.

[Cameronsmith63]

ok, I got it right...For anyone that gets a project and wants to know how to do this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace AmexVisaMasterCardFileGenerator
{
  class Program
  {
    static void Main(string[] args)
    {
      using (OleDbConnection con = 
            new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\\swd\\;"
                                + "Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
        {

        using (OleDbCommand cmd = new OleDbCommand(string.Format
                                  ("SELECT * FROM Book1.csv"), con))
        {
          con.Open();

          // Using a DataTable to process the data
          using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
          {
            using (DataTable tbl = new  DataTable("MyTable"))
            {
              adp.Fill(tbl);

              StringBuilder sb = new StringBuilder();

              foreach (DataRow row in tbl.Rows)
              {
                foreach (DataColumn column in tbl.Columns)
                {
                  sb.Append('"' + row[column].ToString().PadLeft(10, '0') + ',');
                }  

                sb.Append("\r\n");
              }

              File.WriteAllText("C:\\swd\\test.csv", sb.ToString());
            }
          }
          con.Close();
        } 
      }
    }
  }
}

[Cameronsmith63]

ok another update, I started playing around with link:

string path = @"C:\swd\";

        using (OleDbConnection con = 
            new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+ path +";"
                                + "Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
           {
            using (OleDbCommand cmd = new OleDbCommand(string.Format
                                  ("SELECT VisaMasterNbr, AmexNbr FROM Book.csv"), con))
        {
          con.Open();

          using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
          {
            using (DataTable tbl = new  DataTable("MyTable"))
            {
              adp.Fill(tbl);
              StringBuilder sb = new StringBuilder();
              sb.Append("VisaMasterNbr, AmexNbr\n");
              List<string> VisaMasterNbr = tbl.AsEnumerable().Select(x => x[0].ToString().PadLeft(10, '0')).ToList();
              List<string> AmexNbr = tbl.AsEnumerable().Select(x => x[1].ToString().PadLeft(10, '0')).ToList();
              int i = 0;
              foreach (string part in VisaMasterNbr)
              {
                  sb.Append("'" + VisaMasterNbr[i] + ',' + "'" + AmexNbr[i]);
                  sb.Append("\n");
                  i++;
              }
              File.WriteAllText("C:\\swd\\test.csv", sb.ToString());

Only prob im facing now is trying to not let the single quote show in my csv file. I tried the "\"" but then only half of my result set is shown in the csv file.