Gordon  Taylor

Gordon Taylor

1662001740

CSV.js: Simple, Ultra-light (10kb) JS Library for CSV Parsing

CSV.js

Simple, ultra light (10kb uncompressed) javascript CSV library for browser and node with zero dependencies.

Originally developed as part of ReclineJS but now fully standalone.

Usage

Grab the csv.js file and include it in your application.

Depends on jQuery or underscore.deferred (for deferred) in fetch (and jQuery if you need ajax). parse and serialize have zero dependencies.

fetch

A convenient way to load a CSV file from various different sources. fetch supports 3 options depending on the attribute provided on the info argument:

CSV.fetch({
    data: 'raw csv string'
    // or ...
    url: 'url to a csv file'
    // or ...
    file: an HTML 5 file object

    // optional options about structure of the CSV file
    // following the CSV Dialect Description Format 
    // https://frictionlessdata.io/specs/csv-dialect/
    dialect: {
      ...
    }
  }
).done(function(dataset) {
  // dataset object doc'd below
  console.log(dataset);
});

Some more detail on the argument object:

  • data is a string in CSV format. This is passed directly to the CSV parser
  • url: a url to an online CSV file that is ajax accessible (note this usually requires either local or on a server that is CORS enabled). The file is then loaded using jQuery.ajax and parsed using the CSV parser (NB: this requires jQuery) All options generates similar data and use the memory store outcome, that is they return something like:
  • file: is an HTML5 file object. This is opened and parsed with the CSV parser.
  • dialect: hash / dictionary following the same structure as for parse method below.

Returned dataset object looks like:

{  // an array of arrays - one array each row in the CSV  // (excluding header row - i.e. first row)  records: [ [...], [...], ... ],  // list of fields  fields: [ 'field-name-1', 'field-name-2', ... ],  metadata: { may be some metadata e.g. file name } }

Raw parsing

var out = CSV.parse(csvString, dialect);

Converts a Comma Separated Values string into an array of arrays. Each line in the CSV becomes an array.

Empty fields are converted to nulls and non-quoted numbers are converted to integers or floats.

csvString: the csv string to parse

dialect: [optional] hash with keys as per the CSV dialect description format. It also supports the following additional keys:

Serialize

Convert an Object or a simple array of arrays into a Comma Separated Values string.

var out = CSV.serialize(dataToSerialize, dialect);

Returns a string representing the array serialized as a CSV.

dataToSerialize is an Object or array of arrays to convert. Object structure must be as follows:

{
  fields: [ {id: .., ...}, {id: ..., 
  records: [ { record }, { record }, ... ]
  ... // more attributes we do not care about
}

Nulls are converted to empty fields and integers or floats are converted to non-quoted numbers.

You may optionally specify a label inside each field so that the serialized data will use it as the column heading instead of the id.


Other JS CSV Libs

Node

Development

Requirements

  • webpack
  • jquery
npm install
npm install jquery
webpack-dev-server

Run tests

Requirements

  • karma
  • phantomjs
npm -g install karma karma-cli phantomjs-prebuilt
npm install
npm install jquery
npm test

Download Details:

Author: okfn
Source Code: https://github.com/okfn/csv.js 
License: MIT license

#javascript #csv 

CSV.js: Simple, Ultra-light (10kb) JS Library for CSV Parsing

PapaParse: Parse CSV with JavaScript

Parse CSV with JavaScript

Papa Parse is the fastest in-browser CSV (or delimited text) parser for JavaScript. It is reliable and correct according to RFC 4180, and it comes with these features:

  • Easy to use
  • Parse CSV files directly (local or over the network)
  • Fast mode (is really fast)
  • Stream large files (even via HTTP)
  • Reverse parsing (converts JSON to CSV)
  • Auto-detect delimiter
  • Worker threads to keep your web page reactive
  • Header row support
  • Pause, resume, abort
  • Can convert numbers and booleans to their types
  • Optional jQuery integration to get files from <input type="file"> elements
  • One of the only parsers that correctly handles line-breaks and quotations

Papa Parse has no dependencies - not even jQuery.

Install

papaparse is available on npm. It can be installed with the following command:

npm install papaparse

If you don't want to use npm, papaparse.min.js can be downloaded to your project source.

Homepage & Demo

To learn how to use Papa Parse:

The website is hosted on Github Pages. Its content is also included in the docs folder of this repository. If you want to contribute on it just clone the master of this repository and open a pull request.

Papa Parse for Node

Papa Parse can parse a Readable Stream instead of a File when used in Node.js environments (in addition to plain strings). In this mode, encoding must, if specified, be a Node-supported character encoding. The Papa.LocalChunkSize, Papa.RemoteChunkSize , download, withCredentials and worker config options are unavailable.

Papa Parse can also parse in a node streaming style which makes .pipe available. Simply pipe the Readable Stream to the stream returned from Papa.parse(Papa.NODE_STREAM_INPUT, options). The Papa.LocalChunkSize, Papa.RemoteChunkSize , download, withCredentials, worker, step, and complete config options are unavailable. To register a callback with the stream to process data, use the data event like so: stream.on('data', callback) and to signal the end of stream, use the 'end' event like so: stream.on('end', callback).

Get Started

For usage instructions, see the homepage and, for more detail, the documentation.

Tests

Papa Parse is under test. Download this repository, run npm install, then npm test to run the tests.

Contributing

To discuss a new feature or ask a question, open an issue. To fix a bug, submit a pull request to be credited with the contributors! Remember, a pull request, with test, is best. You may also discuss on Twitter with #PapaParse or directly to me, @mholt6.

If you contribute a patch, ensure the tests suite is running correctly. We run continuous integration on each pull request and will not accept a patch that breaks the tests.

Download Details:

Author: mholt
Source Code: https://github.com/mholt/PapaParse 
License: MIT license

#javascript #csv 

PapaParse: Parse CSV with JavaScript

CSVFiles.jl: FileIO.jl integration for CSV files

CSVFiles

Overview

This package provides load and save support for CSV Files under the FileIO.jl package.

Installation

Use Pkg.add("CSVFiles") in Julia to install CSVFiles and its dependencies.

Usage

Load a CSV file

To read a CSV file into a DataFrame, use the following julia code:

using CSVFiles, DataFrames

df = DataFrame(load("data.csv"))

To read a gzipped CSV file into a DataFrame:

using CSVFiles, DataFrames

df = DataFrame(load(File(format"CSV", "data.csv.gz")))

The call to load returns a struct that is an IterableTable.jl, so it can be passed to any function that can handle iterable tables, i.e. all the sinks in IterableTable.jl. Here are some examples of materializing a CSV file into data structures that are not a DataFrame:

using CSVFiles, DataTables, IndexedTables, TimeSeries, Temporal, Gadfly

# Load into a DataTable
dt = DataTable(load("data.csv"))

# Load into an IndexedTable
it = IndexedTable(load("data.csv"))

# Load into a TimeArray
ta = TimeArray(load("data.csv"))

# Load into a TS
ts = TS(load("data.csv"))

# Plot directly with Gadfly
plot(load("data.csv"), x=:a, y=:b, Geom.line)

One can load both local files and files that can be downloaded via either http or https. To download from a remote URL, simply pass a URL to the load function instead of just a filename. In addition one can also load data from an IO object, i.e. any stream. The syntax that scenario is

df = DataFrame(load(Stream(format"CSV", io)))

The load function also takes a number of parameters:

load(f::FileIO.File{FileIO.format"CSV"}; <arguments>...)

For example, to load a CSV file that doesn't have the extension ".csv", you need

load(File(format"CSV", "csv_file.txt"))

Arguments:

  • delim: the delimiter character
  • spacedelim: a Bool indicating whether columns are space delimited. If true, the value of delim is ignored
  • quotechar: character used to quote strings, defaults to "
  • escapechar: character used to escape quotechar in strings. (could be the same as quotechar)
  • commentchar: ignore lines that begin with commentchar
  • row_estimate: estimated number of rows in the file. Defaults to 0 in which case we try to estimate this.
  • skiplines_begin: number of rows to skip at the beginning of the file.
  • header_exists: boolean specifying whether CSV file contains a header
  • colnames: manually specified column names. Could be a vector or a dictionary from Int index (the column) to String column name.
  • colparsers: Parsers to use for specified columns. This can be a vector or a dictionary from column name / column index (Int) to a "parser". The simplest parser is a type such as Int, Float64. It can also be a dateformat"...", see CustomParser if you want to plug in custom parsing behavior
  • type_detect_rows: number of rows to use to infer the initial colparsers defaults to 20.

These are simply the arguments from TextParse.jl, which is used under the hood to read CSV files.

Save a CSV file

The following code saves any iterable table as a CSV file:

using CSVFiles

save("output.csv", it)

This will work as long as it is any of the types supported as sources in IterableTables.jl.

Compressed CSV files can be created by specifying the .gz file extension:

using CSVFiles

save(File(format"CSV", "output.csv.gz"), df)

One can also save into an arbitrary stream:

using CSVFiles

save(Stream(format"CSV", io), it)

The save function takes a number of arguments:

save(f::FileIO.File{FileIO.format"CSV"}, data; delim=',', quotechar='"', escapechar='"', nastring="NA", header=true)

Arguments

  • delim: the delimiter character, defaults to ,.
  • quotechar: character used to quote strings, defaults to ".
  • escapechar: character used to escape quotechar in strings, defaults to \.
  • nastring: string to insert in the place of missing values, defaults to NA.
  • header: whether a header should be written, defaults to ``true.

Using the pipe syntax

Both load and save also support the pipe syntax. For example, to load a CSV file into a DataFrame, one can use the following code:

using CSVFiles, DataFrames

df = load("data.csv") |> DataFrame

To save an iterable table, one can use the following form:

using CSVFiles, DataFrames

df = # Aquire a DataFrame somehow

df |> save("output.csv")

The pipe syntax is especially useful when combining it with Query.jl queries, for example one can easily load a CSV file, pipe it into a query, then pipe it to the save function to store the results in a new file.

Download Details:

Author: queryverse
Source Code: https://github.com/queryverse/CSVFiles.jl 
License: View license

#julia #csv 

CSVFiles.jl: FileIO.jl integration for CSV files
Nat  Grady

Nat Grady

1659720120

Vroom: Fast Reading Of Delimited Files

🏎💨vroom 

The fastest delimited reader for R, 1.23 GB/sec.

But that’s impossible! How can it be so fast?

vroom doesn’t stop to actually read all of your data, it simply indexes where each record is located so it can be read later. The vectors returned use the Altrep framework to lazily load the data on-demand when it is accessed, so you only pay for what you use. This lazy access is done automatically, so no changes to your R data-manipulation code are needed.

vroom also uses multiple threads for indexing, materializing non-character columns, and when writing to further improve performance.

packageversiontime (sec)speedupthroughput
vroom1.5.11.3653.301.23 GB/sec
data.table1.14.05.8312.40281.65 MB/sec
readr1.4.037.301.9444.02 MB/sec
read.delim4.1.072.311.0022.71 MB/sec

Features

vroom has nearly all of the parsing features of readr for delimited and fixed width files, including

  • delimiter guessing*
  • custom delimiters (including multi-byte* and Unicode* delimiters)
  • specification of column types (including type guessing)
    • numeric types (double, integer, big integer*, number)
    • logical types
    • datetime types (datetime, date, time)
    • categorical types (characters, factors)
  • column selection, like dplyr::select()*
  • skipping headers, comments and blank lines
  • quoted fields
  • double and backslashed escapes
  • whitespace trimming
  • windows newlines
  • reading from multiple files or connections*
  • embedded newlines in headers and fields**
  • writing delimited files with as-needed quoting.
  • robust to invalid inputs (vroom has been extensively tested with the afl fuzz tester)*.

* these are additional features not in readr.

** requires num_threads = 1.

Installation

Install vroom from CRAN with:

install.packages("vroom")

Alternatively, if you need the development version from GitHub install it with:

# install.packages("devtools")
devtools::install_dev("vroom")

Usage

See getting started to jump start your use of vroom!

vroom uses the same interface as readr to specify column types.

vroom::vroom("mtcars.tsv",
  col_types = list(cyl = "i", gear = "f",hp = "i", disp = "_",
                   drat = "_", vs = "l", am = "l", carb = "i")
)
#> # A tibble: 32 × 10
#>   model           mpg   cyl    hp    wt  qsec vs    am    gear   carb
#>   <chr>         <dbl> <int> <int> <dbl> <dbl> <lgl> <lgl> <fct> <int>
#> 1 Mazda RX4      21       6   110  2.62  16.5 FALSE TRUE  4         4
#> 2 Mazda RX4 Wag  21       6   110  2.88  17.0 FALSE TRUE  4         4
#> 3 Datsun 710     22.8     4    93  2.32  18.6 TRUE  TRUE  4         1
#> # … with 29 more rows

Reading multiple files

vroom natively supports reading from multiple files (or even multiple connections!).

First we generate some files to read by splitting the nycflights dataset by airline.

library(nycflights13)
purrr::iwalk(
  split(flights, flights$carrier),
  ~ { .x$carrier[[1]]; vroom::vroom_write(.x, glue::glue("flights_{.y}.tsv"), delim = "\t") }
)

Then we can efficiently read them into one tibble by passing the filenames directly to vroom.

files <- fs::dir_ls(glob = "flights*tsv")
files
#> flights_9E.tsv flights_AA.tsv flights_AS.tsv flights_B6.tsv flights_DL.tsv 
#> flights_EV.tsv flights_F9.tsv flights_FL.tsv flights_HA.tsv flights_MQ.tsv 
#> flights_OO.tsv flights_UA.tsv flights_US.tsv flights_VX.tsv flights_WN.tsv 
#> flights_YV.tsv
vroom::vroom(files)
#> Rows: 336776 Columns: 19
#> ── Column specification ────────────────────────────────────────────────────────
#> Delimiter: "\t"
#> chr   (4): carrier, tailnum, origin, dest
#> dbl  (14): year, month, day, dep_time, sched_dep_time, dep_delay, arr_time, ...
#> dttm  (1): time_hour
#> 
#> ℹ Use `spec()` to retrieve the full column specification for this data.
#> ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
#> # A tibble: 336,776 × 19
#>    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
#>   <dbl> <dbl> <dbl>    <dbl>          <dbl>     <dbl>    <dbl>          <dbl>
#> 1  2013     1     1      810            810         0     1048           1037
#> 2  2013     1     1     1451           1500        -9     1634           1636
#> 3  2013     1     1     1452           1455        -3     1637           1639
#> # … with 336,773 more rows, and 11 more variables: arr_delay <dbl>,
#> #   carrier <chr>, flight <dbl>, tailnum <chr>, origin <chr>, dest <chr>,
#> #   air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>

Learning more

Benchmarks

The speed quoted above is from a real 1.53G dataset with 14,388,451 rows and 11 columns, see the benchmark article for full details of the dataset and bench/ for the code used to retrieve the data and perform the benchmarks.

Environment variables

In addition to the arguments to the vroom() function, you can control the behavior of vroom with a few environment variables. Generally these will not need to be set by most users.

  • VROOM_TEMP_PATH - Path to the directory used to store temporary files when reading from a R connection. If unset defaults to the R session’s temporary directory (tempdir()).
  • VROOM_THREADS - The number of processor threads to use when indexing and parsing. If unset defaults to parallel::detectCores().
  • VROOM_SHOW_PROGRESS - Whether to show the progress bar when indexing. Regardless of this setting the progress bar is disabled in non-interactive settings, R notebooks, when running tests with testthat and when knitting documents.
  • VROOM_CONNECTION_SIZE - The size (in bytes) of the connection buffer when reading from connections (default is 128 KiB).
  • VROOM_WRITE_BUFFER_LINES - The number of lines to use for each buffer when writing files (default: 1000).

There are also a family of variables to control use of the Altrep framework. For versions of R where the Altrep framework is unavailable (R < 3.5.0) they are automatically turned off and the variables have no effect. The variables can take one of true, false, TRUE, FALSE, 1, or 0.

  • VROOM_USE_ALTREP_NUMERICS - If set use Altrep for all numeric types (default false).

There are also individual variables for each type. Currently only VROOM_USE_ALTREP_CHR defaults to true.

  • VROOM_USE_ALTREP_CHR
  • VROOM_USE_ALTREP_FCT
  • VROOM_USE_ALTREP_INT
  • VROOM_USE_ALTREP_BIG_INT
  • VROOM_USE_ALTREP_DBL
  • VROOM_USE_ALTREP_NUM
  • VROOM_USE_ALTREP_LGL
  • VROOM_USE_ALTREP_DTTM
  • VROOM_USE_ALTREP_DATE
  • VROOM_USE_ALTREP_TIME

RStudio caveats

RStudio’s environment pane calls object.size() when it refreshes the pane, which for Altrep objects can be extremely slow. RStudio 1.2.1335+ includes the fixes (RStudio#4210, RStudio#4292) for this issue, so it is recommended you use at least that version.

Thanks

Author: Tidyverse
Source Code: https://github.com/tidyverse/vroom 
License: Unknown, MIT licenses found

#r #csv 

Vroom: Fast Reading Of Delimited Files
Nat  Grady

Nat Grady

1659697920

Leeper/rio: A Swiss-Army Knife for Data I/O

rio: A Swiss-Army Knife for Data I/O 

Overview

The aim of rio is to make data file I/O in R as easy as possible by implementing four simple functions in Swiss-army knife style:

  • import() provides a painless data import experience by automatically choosing the appropriate import/read function based on file extension (or a specified format argument)
  • import_list() imports a list of data frames from a multi-object file (Excel workbook, .Rdata files, zip directory, or HTML file)
  • export() provides the same painless file recognition for data export/write functionality
  • convert() wraps import() and export() to allow the user to easily convert between file formats (thus providing a FOSS replacement for programs like Stat/Transfer or Sledgehammer). Relatedly, Luca Braglia has created a Shiny app called rioweb that provides access to the file conversion features of rio. GREA is an RStudio add-in that provides an interactive interface for reading in data using rio.

Installation

The package is available on CRAN and can be installed directly in R using install.packages(). You may want to run install_formats() after the first installation.

install.packages("rio")
install_formats()

The latest development version on GitHub can be installed using:

if (!require("remotes")){
    install.packages("remotes")
}
remotes::install_github("leeper/rio")

Usage

Because rio is meant to streamline data I/O, the package is extremely easy to use. Here are some examples of reading, writing, and converting data files.

Export

Exporting data is handled with one function, export():

library("rio")

export(mtcars, "mtcars.csv") # comma-separated values
export(mtcars, "mtcars.rds") # R serialized
export(mtcars, "mtcars.sav") # SPSS

A particularly useful feature of rio is the ability to import from and export to compressed (e.g., zip) directories, saving users the extra step of compressing a large exported file, e.g.:

export(mtcars, "mtcars.tsv.zip")

As of rio v0.5.0, export() can also write multiple data frames to respective sheets of an Excel workbook or an HTML file:

export(list(mtcars = mtcars, iris = iris), file = "mtcars.xlsx")

Import

Importing data is handled with one function, import():

x <- import("mtcars.csv")
y <- import("mtcars.rds")
z <- import("mtcars.sav")

# confirm data match
all.equal(x, y, check.attributes = FALSE)
## [1] TRUE
all.equal(x, z, check.attributes = FALSE)
## [1] TRUE

Note: Because of inconsistencies across underlying packages, the data.frame returned by import might vary slightly (in variable classes and attributes) depending on file type.

In rio v0.5.0, a new list-based import function was added. This allows users to import a list of data frames from a multi-object file (such as an Excel workbook, .Rdata file, zip directory, or HTML file):

str(m <- import_list("mtcars.xlsx"))
## List of 2
##  $ mtcars:'data.frame':  32 obs. of  11 variables:
##   ..$ mpg : num [1:32] 21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ...
##   ..$ cyl : num [1:32] 6 6 4 6 8 6 8 4 4 6 ...
##   ..$ disp: num [1:32] 160 160 108 258 360 ...
##   ..$ hp  : num [1:32] 110 110 93 110 175 105 245 62 95 123 ...
##   ..$ drat: num [1:32] 3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ...
##   ..$ wt  : num [1:32] 2.62 2.88 2.32 3.21 3.44 ...
##   ..$ qsec: num [1:32] 16.5 17 18.6 19.4 17 ...
##   ..$ vs  : num [1:32] 0 0 1 1 0 1 0 1 1 1 ...
##   ..$ am  : num [1:32] 1 1 1 0 0 0 0 0 0 0 ...
##   ..$ gear: num [1:32] 4 4 4 3 3 3 3 4 4 4 ...
##   ..$ carb: num [1:32] 4 4 1 1 2 1 4 2 2 4 ...
##  $ iris  :'data.frame':  150 obs. of  5 variables:
##   ..$ Sepal.Length: num [1:150] 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...
##   ..$ Sepal.Width : num [1:150] 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...
##   ..$ Petal.Length: num [1:150] 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...
##   ..$ Petal.Width : num [1:150] 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...
##   ..$ Species     : chr [1:150] "setosa" "setosa" "setosa" "setosa" ...

And for rio v0.6.0, a new list-based export function was added. This makes it easy to export a list of (possibly named) data frames to multiple files:

export_list(m, "%s.tsv")
c("mtcars.tsv", "iris.tsv") %in% dir()
## [1] TRUE TRUE

Convert

The convert() function links import() and export() by constructing a dataframe from the imported file and immediately writing it back to disk. convert() invisibly returns the file name of the exported file, so that it can be used to programmatically access the new file.

convert("mtcars.sav", "mtcars.dta")

It is also possible to use rio on the command-line by calling Rscript with the -e (expression) argument. For example, to convert a file from Stata (.dta) to comma-separated values (.csv), simply do the following:

Rscript -e "rio::convert('iris.dta', 'iris.csv')"

Supported file formats

rio supports a wide range of file formats. To keep the package slim, all non-essential formats are supported via “Suggests” packages, which are not installed (or loaded) by default. To ensure rio is fully functional, install these packages the first time you use rio via:

install_formats()

The full list of supported formats is below:

FormatTypical ExtensionImport PackageExport PackageInstalled by Default
Comma-separated data.csvdata.tabledata.tableYes
Pipe-separated data.psvdata.tabledata.tableYes
Tab-separated data.tsvdata.tabledata.tableYes
CSVY (CSV + YAML metadata header).csvydata.tabledata.tableYes
SAS.sas7bdathavenhavenYes
SPSS.savhavenhavenYes
SPSS (compressed).zsavhavenhavenYes
Stata.dtahavenhavenYes
SAS XPORT.xpthavenhavenYes
SPSS Portable.porhaven Yes
Excel.xlsreadxl Yes
Excel.xlsxreadxlopenxlsxYes
R syntax.RbasebaseYes
Saved R objects.RData, .rdabasebaseYes
Serialized R objects.rdsbasebaseYes
Epiinfo.recforeign Yes
Minitab.mtpforeign Yes
Systat.sydforeign Yes
“XBASE” database files.dbfforeignforeignYes
Weka Attribute-Relation File Format.arffforeignforeignYes
Data Interchange Format.difutils Yes
Fortran datano recognized extensionutils Yes
Fixed-width format data.fwfutilsutilsYes
gzip comma-separated data.csv.gzutilsutilsYes
Apache Arrow (Parquet).parquetarrowarrowNo
EViews.wf1hexView No
Feather R/Python interchange format.featherfeatherfeatherNo
Fast Storage.fstfstfstNo
JSON.jsonjsonlitejsonliteNo
Matlab.matrmatiormatioNo
OpenDocument Spreadsheet.odsreadODSreadODSNo
HTML Tables.htmlxml2xml2No
Shallow XML documents.xmlxml2xml2No
YAML.ymlyamlyamlNo
Clipboarddefault is tsvcliprcliprNo
Google Sheetsas Comma-separated data   
Graphpad Prism.pzfxpzfxpzfxNo

Additionally, any format that is not supported by rio but that has a known R implementation will produce an informative error message pointing to a package and import or export function. Unrecognized formats will yield a simple “Unrecognized file format” error.

Package Philosophy

The core advantage of rio is that it makes assumptions that the user is probably willing to make. Eight of these are important:

  1. rio uses the file extension of a file name to determine what kind of file it is. This is the same logic used by Windows OS, for example, in determining what application is associated with a given file type. By removing the need to manually match a file type (which a beginner may not recognize) to a particular import or export function, rio allows almost all common data formats to be read with the same function. And if a file extension is incorrect, users can force a particular import method by specifying the format argument. Other packages do this as well, but rio aims to be more complete and more consistent than each:
  • reader handles certain text formats and R binary files
  • io offers a set of custom formats
  • ImportExport focuses on select binary formats (Excel, SPSS, and Access files) and provides a Shiny interface.
  • SchemaOnRead iterates through a large number of possible import methods until one works successfully

rio uses data.table::fread() for text-delimited files to automatically determine the file format regardless of the extension. So, a CSV that is actually tab-separated will still be correctly imported. It’s also crazy fast.

rio, wherever possible, does not import character strings as factors.

rio supports web-based imports natively, including from SSL (HTTPS) URLs, from shortened URLs, from URLs that lack proper extensions, and from (public) Google Documents Spreadsheets.

rio imports from from single-file .zip and .tar archives automatically, without the need to explicitly decompress them. Export to compressed directories is also supported.

rio wraps a variety of faster, more stream-lined I/O packages than those provided by base R or the foreign package. It uses data.table for delimited formats, haven for SAS, Stata, and SPSS files, smarter and faster fixed-width file import and export routines, and readxl and openxlsx for reading and writing Excel workbooks.

rio stores metadata from rich file formats (SPSS, Stata, etc.) in variable-level attributes in a consistent form regardless of file type or underlying import function. These attributes are identified as:

  • label: a description of variable
  • labels: a vector mapping numeric values to character strings those values represent
  • format: a character string describing the variable storage type in the original file

rio imports and exports files based on an internal S3 class infrastructure. This means that other packages can contain extensions to rio by registering S3 methods. These methods should take the form .import.rio_X() and .export.rio_X(), where X is the file extension of a file type. An example is provided in the rio.db package.

Author: leeper
Source Code: https://github.com/leeper/rio 

#r #data-science #csv 

Leeper/rio: A Swiss-Army Knife for Data I/O
Nat  Grady

Nat Grady

1659690480

Readr: Read flat files (csv, tsv, fwf) into R

readr

Overview

The goal of readr is to provide a fast and friendly way to read rectangular data from delimited files, such as comma-separated values (CSV) and tab-separated values (TSV). It is designed to parse many types of data found in the wild, while providing an informative problem report when parsing leads to unexpected results. If you are new to readr, the best place to start is the data import chapter in R for Data Science.

Installation

# The easiest way to get readr is to install the whole tidyverse:
install.packages("tidyverse")

# Alternatively, install just readr:
install.packages("readr")
# Or you can install the development version from GitHub:
# install.packages("devtools")
devtools::install_github("tidyverse/readr")

Cheatsheet

Usage

readr is part of the core tidyverse, so you can load it with:

library(tidyverse)
#> ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──
#> ✓ ggplot2 3.3.5          ✓ purrr   0.3.4     
#> ✓ tibble  3.1.6          ✓ dplyr   1.0.7     
#> ✓ tidyr   1.1.4          ✓ stringr 1.4.0     
#> ✓ readr   2.1.2.9000     ✓ forcats 0.5.1
#> ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
#> x dplyr::filter() masks stats::filter()
#> x dplyr::lag()    masks stats::lag()

Of course, you can also load readr as an individual package:

library(readr)

To read a rectangular dataset with readr, you combine two pieces: a function that parses the lines of the file into individual fields and a column specification.

readr supports the following file formats with these read_*() functions:

  • read_csv(): comma-separated values (CSV) files
  • read_tsv(): tab-separated values (TSV) files
  • read_delim(): delimited files (CSV and TSV are important special cases)
  • read_fwf(): fixed-width files
  • read_table(): whitespace-separated files
  • read_log(): web log files

A column specification describes how each column should be converted from a character vector to a specific data type (e.g. character, numeric, datetime, etc.). In the absence of a column specification, readr will guess column types from the data. vignette("column-types") gives more detail on how readr guesses the column types. Column type guessing is very handy, especially during data exploration, but it’s important to remember these are just guesses. As any data analysis project matures past the exploratory phase, the best strategy is to provide explicit column types.

The following example loads a sample file bundled with readr and guesses the column types:

(chickens <- read_csv(readr_example("chickens.csv")))
#> Rows: 5 Columns: 4
#> ── Column specification ────────────────────────────────────────────────────────
#> Delimiter: ","
#> chr (3): chicken, sex, motto
#> dbl (1): eggs_laid
#> 
#> ℹ Use `spec()` to retrieve the full column specification for this data.
#> ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
#> # A tibble: 5 × 4
#>   chicken                 sex     eggs_laid motto                               
#>   <chr>                   <chr>       <dbl> <chr>                               
#> 1 Foghorn Leghorn         rooster         0 That's a joke, ah say, that's a jok…
#> 2 Chicken Little          hen             3 The sky is falling!                 
#> 3 Ginger                  hen            12 Listen. We'll either die free chick…
#> 4 Camilla the Chicken     hen             7 Bawk, buck, ba-gawk.                
#> 5 Ernie The Giant Chicken rooster         0 Put Captain Solo in the cargo hold.

Note that readr prints the column types – the guessed column types, in this case. This is useful because it allows you to check that the columns have been read in as you expect. If they haven’t, that means you need to provide the column specification. This sounds like a lot of trouble, but luckily readr affords a nice workflow for this. Use spec() to retrieve the (guessed) column specification from your initial effort.

spec(chickens)
#> cols(
#>   chicken = col_character(),
#>   sex = col_character(),
#>   eggs_laid = col_double(),
#>   motto = col_character()
#> )

Now you can copy, paste, and tweak this, to create a more explicit readr call that expresses the desired column types. Here we express that sex should be a factor with levels rooster and hen, in that order, and that eggs_laid should be integer.

chickens <- read_csv(
  readr_example("chickens.csv"),
  col_types = cols(
    chicken   = col_character(),
    sex       = col_factor(levels = c("rooster", "hen")),
    eggs_laid = col_integer(),
    motto     = col_character()
  )
)
chickens
#> # A tibble: 5 × 4
#>   chicken                 sex     eggs_laid motto                               
#>   <chr>                   <fct>       <int> <chr>                               
#> 1 Foghorn Leghorn         rooster         0 That's a joke, ah say, that's a jok…
#> 2 Chicken Little          hen             3 The sky is falling!                 
#> 3 Ginger                  hen            12 Listen. We'll either die free chick…
#> 4 Camilla the Chicken     hen             7 Bawk, buck, ba-gawk.                
#> 5 Ernie The Giant Chicken rooster         0 Put Captain Solo in the cargo hold.

vignette("readr") gives an expanded introduction to readr.

Editions

readr got a new parsing engine in version 2.0.0 (released July 2021). In this so-called second edition, readr calls vroom::vroom(), by default.

The parsing engine in readr versions prior to 2.0.0 is now called the first edition. If you’re using readr >= 2.0.0, you can still access first edition parsing via the functions with_edition(1, ...) and local_edition(1). And, obviously, if you’re using readr < 2.0.0, you will get first edition parsing, by definition, because that’s all there is.

We will continue to support the first edition for a number of releases, but the overall goal is to make the second edition uniformly better than the first. Therefore the plan is to eventually deprecate and then remove the first edition code. New code and actively-maintained code should use the second edition. The workarounds with_edition(1, ...) and local_edition(1) are offered as a pragmatic way to patch up legacy code or as a temporary solution for infelicities identified as the second edition matures.

Alternatives

There are two main alternatives to readr: base R and data.table’s fread(). The most important differences are discussed below.

Base R

Compared to the corresponding base functions, readr functions:

Use a consistent naming scheme for the parameters (e.g. col_names and col_types not header and colClasses).

Are generally much faster (up to 10x-100x) depending on the dataset.

Leave strings as is by default, and automatically parse common date/time formats.

Have a helpful progress bar if loading is going to take a while.

All functions work exactly the same way regardless of the current locale. To override the US-centric defaults, use locale().

data.table and fread()

data.table has a function similar to read_csv() called fread(). Compared to fread(), readr functions:

Are sometimes slower, particularly on numeric heavy data.

Can automatically guess some parameters, but basically encourage explicit specification of, e.g., the delimiter, skipped rows, and the header row.

Follow tidyverse-wide conventions, such as returning a tibble, a standard approach for column name repair, and a common mini-language for column selection.

Acknowledgements

Thanks to:

Joe Cheng for showing me the beauty of deterministic finite automata for parsing, and for teaching me why I should write a tokenizer.

JJ Allaire for helping me come up with a design that makes very few copies, and is easy to extend.

Dirk Eddelbuettel for coming up with the name!

Author: Ridyverse
Source Code: https://github.com/tidyverse/readr 
License: Unknown, MIT licenses found

#r #csv 

Readr: Read flat files (csv, tsv, fwf) into R
Audra  Haag

Audra Haag

1659301080

How to Export MySQL Data in CSV format By Date Range with PHP

In this tutorial, I show how you can export MySQL database data in CSV format by date range with PHP. I am using jQuery UI for datepicker.

Source: https://makitweb.com

#php #mysql #csv 

How to Export MySQL Data in CSV format By Date Range with PHP
Thai  Son

Thai Son

1659293700

Cách Xuất Dữ Liệu MySQL Ở Định Dạng CSV Theo Phạm Vi Ngày Bằng PHP

Tệp CSV được sử dụng để nhập, xuất dữ liệu và tạo báo cáo.

Nếu bạn có sẵn dữ liệu khổng lồ trong cơ sở dữ liệu MySQL và bạn chỉ yêu cầu một dữ liệu phạm vi ngày cụ thể nhưng tệp chứa tất cả các bản ghi và bạn cần nó theo ý mình.

Bằng cách thêm bộ lọc ngày vào biểu mẫu, bạn chỉ cần chọn phạm vi và xuất nó.

Trong hướng dẫn này, tôi chỉ cách bạn có thể xuất dữ liệu cơ sở dữ liệu MySQL ở định dạng CSV theo phạm vi ngày bằng PHP. Tôi đang sử dụng giao diện người dùng jQuery cho datepicker.

1. Cấu trúc bảng

Tôi đang sử dụng employeebảng trong ví dụ.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL,
  `salary` varchar(30) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(70) NOT NULL,
  `date_of_joining` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Cấu hình

Tạo mới config.php để xác định kết nối cơ sở dữ liệu.

Mã đã hoàn thành

<?php
$host = "localhost"; /* Host name */$user = "root"; /* User */$password = ""; /* Password */$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

3. Tải xuống và bao gồm

  • Tải xuống thư viện giao diện người dùng jQueryjQuery .
  • Bao gồm jquery-ui.css, thư viện jQuery và tập lệnh jquery-ui.min.js.
<!-- jQuery UI CSS -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">

<!-- jQuery --> 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- jQuery UI JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

4. HTML và PHP

Tạo một <form method='post' action='download.php' >. Thêm hai yếu tố văn bản và một nút gửi.

Phần tử văn bản được sử dụng cho bộ chọn ngày.

Tôi đã tạo <table >để liệt kê tất cả các bản ghi của employeebảng.

Mã đã hoàn thành

<div >

   <form method='post' action='download.php'>

     <!-- Datepicker -->
     <input type='text' class='datepicker' placeholder="From date" name="from_date" id='from_date' readonly>
     <input type='text' class='datepicker' placeholder="To date" name="to_date" id='to_date' readonly>

     <!-- Export button -->
     <input type='submit' value='Export' name='Export'>
   </form> 
   <table border='1' style='border-collapse:collapse;'>
     <tr>
       <th>ID</th>
       <th>Employee Name</th>
       <th>Salary</th>
       <th>Gender</th>
       <th>City</th>
       <th>Email</th>
       <th>Date of joining</th>
     </tr>
     <?php 
     $query = "SELECT * FROM employee ORDER BY id asc";
     $result = mysqli_query($con,$query);
     
     while($row = mysqli_fetch_assoc($result)){
       $id = $row['id'];
       $emp_name = $row['emp_name'];
       $salary = $row['salary'];
       $gender = $row['gender'];
       $city = $row['city'];
       $email = $row['email'];
       $date_of_joining = $row['date_of_joining'];

     ?>
     <tr>
        <td><?= $id; ?></td>
        <td><?= $emp_name; ?></td>
        <td><?= $salary; ?></td>
        <td><?= $gender; ?></td>
        <td><?= $city; ?></td>
        <td><?= $email; ?></td>
        <td><?= $date_of_joining; ?></td>
     </tr>
     <?php
     }
     ?>
  </table>
</div>

5. Tạo và tải xuống tệp CSV

Tạo download.phptệp để tạo CSV và tải xuống.

Đọc POST from_dateto_dategán nó cho các biến.

Tạo một truy vấn SELECT để tìm nạp các bản ghi từ employeebảng. Theo mặc định, hãy chọn tất cả các bản ghi nếu from_dateto_date are not set.

Nếu cả hai được đặt thì sử dụng betweenon date_of_joiningtrong mệnh đề WHERE để chọn bản ghi.

Mở tệp ở chế độ ghi và ghi các cột tiêu đề vào đó.

Lặp lại các bản ghi đã tìm nạp và khởi tạo $employee_arrMảng với các giá trị cần thiết và ghi vào tệp.

Sau khi tạo tệp thành công, hãy chuẩn bị tệp để tải xuống và xóa tệp sau khi tải xuống bằng  unlink() phương pháp.

Mã đã hoàn thành

<?php
include "config.php";
$filename = 'employee_'.time().'.csv';

// POST values
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];

// Select query
$query = "SELECT * FROM employee ORDER BY id asc";

if(isset($_POST['from_date']) && isset($_POST['to_date'])){
   $query = "SELECT * FROM employee where date_of_joining between '".$from_date."' and '".$to_date."' ORDER BY id asc";
}

$result = mysqli_query($con,$query);
$employee_arr = array();

// file creation
$file = fopen($filename,"w");

// Header row - Remove this code if you don't want a header row in the export file.
$employee_arr = array("id","Employee Name","Salary","Gender","City","Email","Date of Joining"); 

while($row = mysqli_fetch_assoc($result)){
   $id = $row['id'];
   $emp_name = $row['emp_name'];
   $salary = $row['salary'];
   $gender = $row['gender'];
   $city = $row['city'];
   $email = $row['email'];
   $date_of_joining = $row['date_of_joining'];

   // Write to file 
   $employee_arr = array($id,$emp_name,$salary,$gender,$city,$email,$date_of_joining);
   fputcsv($file,$employee_arr); 
}

fclose($file);

// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/csv; ");

readfile($filename);

// deleting file
unlink($filename);
exit();

6. jQuery

Khởi tạo bộ chọn ngày giao diện người dùng jQuery trên #from_date#to_date. Đặt định dạng ngày "yy-mm-dd"và bật thay đổi năm.

Thêm xác nhận vào lựa chọn ngày bằng cách sử dụng onSelecttùy chọn.

Nếu fromngày được chọn thì không cho phép tongày phải nhỏ hơn fromngày bằng minDatetùy chọn cài đặt #to_datetương tự, nếu tongày được chọn thì không cho phép fromngày lớn hơn tongày bằng maxDatetùy chọn cài đặt của #from_date.

Mã đã hoàn thành

$(document).ready(function(){

   // From datepicker
   $("#from_date").datepicker({ 
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() + 1);
         $("#to_date").datepicker("option", "minDate", dt);
      }
   });

   // To datepicker
   $("#to_date").datepicker({
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() - 1);
         $("#from_date").datepicker("option", "maxDate", dt);
      }
   });
});

7. Demo

Xem Demo


8. Kết luận

ĐĂNG các giá trị tìm kiếm ngày tháng và sử dụng betweenđể CHỌN bản ghi. Sử dụng fputcsv()hàm để ghi dữ liệu vào tệp.

Nguồn:  https://makitweb.com

#php #mysql #csv 

Cách Xuất Dữ Liệu MySQL Ở Định Dạng CSV Theo Phạm Vi Ngày Bằng PHP

Экспорт данных MySQL в формате CSV по диапазону дат с помощью PHP

Файл CSV используется для импорта данных, экспорта и создания отчета.

Если у вас есть огромные данные, доступные в базе данных MySQL, и вам нужны только данные определенного диапазона дат, но файл содержит все записи, и вам это нужно самостоятельно.

Добавив фильтр даты в форму, вам просто нужно выбрать диапазон и экспортировать его.

В этом руководстве я покажу, как вы можете экспортировать данные базы данных MySQL в формате CSV по диапазону дат с помощью PHP. Я использую jQuery UI для выбора даты.

1. Структура таблицы

Я использую employeeтаблицу в примере.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL,
  `salary` varchar(30) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(70) NOT NULL,
  `date_of_joining` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Конфигурация

Создайте новый config.php , чтобы определить подключение к базе данных.

Завершенный код

<?php
$host = "localhost"; /* Host name */$user = "root"; /* User */$password = ""; /* Password */$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

3. Загрузите и включите

  • Загрузите библиотеки jQuery и jQuery UI .
  • Включите jquery-ui.css, библиотеку jQuery и скрипт jquery-ui.min.js.
<!-- jQuery UI CSS -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">

<!-- jQuery --> 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- jQuery UI JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

4. HTML и PHP

Создайте <form method='post' action='download.php' >. Добавьте два текстовых элемента и кнопку отправки.

Текстовый элемент используется для выбора даты.

Я создал <table >список всех записей employeeтаблицы.

Завершенный код

<div >

   <form method='post' action='download.php'>

     <!-- Datepicker -->
     <input type='text' class='datepicker' placeholder="From date" name="from_date" id='from_date' readonly>
     <input type='text' class='datepicker' placeholder="To date" name="to_date" id='to_date' readonly>

     <!-- Export button -->
     <input type='submit' value='Export' name='Export'>
   </form> 
   <table border='1' style='border-collapse:collapse;'>
     <tr>
       <th>ID</th>
       <th>Employee Name</th>
       <th>Salary</th>
       <th>Gender</th>
       <th>City</th>
       <th>Email</th>
       <th>Date of joining</th>
     </tr>
     <?php 
     $query = "SELECT * FROM employee ORDER BY id asc";
     $result = mysqli_query($con,$query);
     
     while($row = mysqli_fetch_assoc($result)){
       $id = $row['id'];
       $emp_name = $row['emp_name'];
       $salary = $row['salary'];
       $gender = $row['gender'];
       $city = $row['city'];
       $email = $row['email'];
       $date_of_joining = $row['date_of_joining'];

     ?>
     <tr>
        <td><?= $id; ?></td>
        <td><?= $emp_name; ?></td>
        <td><?= $salary; ?></td>
        <td><?= $gender; ?></td>
        <td><?= $city; ?></td>
        <td><?= $email; ?></td>
        <td><?= $date_of_joining; ?></td>
     </tr>
     <?php
     }
     ?>
  </table>
</div>

5. Создайте и загрузите CSV-файл

Создайте download.phpфайл для создания CSV и загрузите его.

Прочитайте POST from_dateи to_dateназначьте его переменным.

Создайте запрос SELECT для извлечения записей из employeeтаблицы. По умолчанию выбрать все записи, если from_dateиto_date are not set.

Если оба установлены, используйте betweenпредложение date_of_joiningWHERE для выбора записей.

Откройте файл в режиме записи и запишите в него столбцы заголовков.

Зациклить на извлеченных записях и инициализировать $employee_arrмассив требуемыми значениями и записать в файл.

После успешного создания файла подготовьте его к загрузке и удалите после загрузки с помощью  unlink() метода .

Завершенный код

<?php
include "config.php";
$filename = 'employee_'.time().'.csv';

// POST values
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];

// Select query
$query = "SELECT * FROM employee ORDER BY id asc";

if(isset($_POST['from_date']) && isset($_POST['to_date'])){
   $query = "SELECT * FROM employee where date_of_joining between '".$from_date."' and '".$to_date."' ORDER BY id asc";
}

$result = mysqli_query($con,$query);
$employee_arr = array();

// file creation
$file = fopen($filename,"w");

// Header row - Remove this code if you don't want a header row in the export file.
$employee_arr = array("id","Employee Name","Salary","Gender","City","Email","Date of Joining"); 

while($row = mysqli_fetch_assoc($result)){
   $id = $row['id'];
   $emp_name = $row['emp_name'];
   $salary = $row['salary'];
   $gender = $row['gender'];
   $city = $row['city'];
   $email = $row['email'];
   $date_of_joining = $row['date_of_joining'];

   // Write to file 
   $employee_arr = array($id,$emp_name,$salary,$gender,$city,$email,$date_of_joining);
   fputcsv($file,$employee_arr); 
}

fclose($file);

// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/csv; ");

readfile($filename);

// deleting file
unlink($filename);
exit();

6. jQuery

Инициализируйте средство выбора даты пользовательского интерфейса jQuery на #from_dateи #to_date. Установите формат даты "yy-mm-dd"и включите изменение года.

Добавьте проверку при выборе даты с помощью onSelectопции.

Если fromвыбрана дата, то дата не toдолжна быть меньше fromдаты, установив minDateпараметр #to_dateаналогичным образом, если toдата выбрана, то не разрешить fromдату быть больше toдаты, установив maxDateпараметр #from_date.

Завершенный код

$(document).ready(function(){

   // From datepicker
   $("#from_date").datepicker({ 
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() + 1);
         $("#to_date").datepicker("option", "minDate", dt);
      }
   });

   // To datepicker
   $("#to_date").datepicker({
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() - 1);
         $("#from_date").datepicker("option", "maxDate", dt);
      }
   });
});

7. Демо

Посмотреть демо


8. Заключение

POST значения поиска по дате и используйте betweenдля SELECT записей. Используйте fputcsv()функцию для записи данных в файл.

Источник:  https://makitweb.com

#php #mysql #csv 

Экспорт данных MySQL в формате CSV по диапазону дат с помощью PHP

如何使用 PHP 按日期範圍以 CSV 格式導出 MySQL 數據

CSV 文件用於數據導入、導出和生成報告。

如果您在 MySQL 數據庫中有大量可用數據,並且您只需要特定日期範圍的數據,但該文件包含所有記錄,您自己需要它。

通過向表單添加日期過濾器,您只需選擇範圍並將其導出。

在本教程中,我將展示如何使用 PHP 按日期範圍以 CSV 格式導出 MySQL 數據庫數據。我正在為日期選擇器使用 jQuery UI。

1.表結構

employee在示例中使用表格。

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL,
  `salary` varchar(30) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(70) NOT NULL,
  `date_of_joining` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2.配置

創建一個新config.php 的來定義數據庫連接。

完成的代碼

<?php
$host = "localhost"; /* Host name */$user = "root"; /* User */$password = ""; /* Password */$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

3. 下載並包含

  • 下載jQueryjQuery UI庫。
  • 包括 jquery-ui.css、jQuery 庫和 jquery-ui.min.js 腳本。
<!-- jQuery UI CSS -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">

<!-- jQuery --> 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- jQuery UI JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

4. HTML 和 PHP

創建一個<form method='post' action='download.php' >. 添加兩個文本元素和一個提交按鈕。

文本元素用於日期選擇器。

我創建<table >以列出表的所有記錄employee

完成的代碼

<div >

   <form method='post' action='download.php'>

     <!-- Datepicker -->
     <input type='text' class='datepicker' placeholder="From date" name="from_date" id='from_date' readonly>
     <input type='text' class='datepicker' placeholder="To date" name="to_date" id='to_date' readonly>

     <!-- Export button -->
     <input type='submit' value='Export' name='Export'>
   </form> 
   <table border='1' style='border-collapse:collapse;'>
     <tr>
       <th>ID</th>
       <th>Employee Name</th>
       <th>Salary</th>
       <th>Gender</th>
       <th>City</th>
       <th>Email</th>
       <th>Date of joining</th>
     </tr>
     <?php 
     $query = "SELECT * FROM employee ORDER BY id asc";
     $result = mysqli_query($con,$query);
     
     while($row = mysqli_fetch_assoc($result)){
       $id = $row['id'];
       $emp_name = $row['emp_name'];
       $salary = $row['salary'];
       $gender = $row['gender'];
       $city = $row['city'];
       $email = $row['email'];
       $date_of_joining = $row['date_of_joining'];

     ?>
     <tr>
        <td><?= $id; ?></td>
        <td><?= $emp_name; ?></td>
        <td><?= $salary; ?></td>
        <td><?= $gender; ?></td>
        <td><?= $city; ?></td>
        <td><?= $email; ?></td>
        <td><?= $date_of_joining; ?></td>
     </tr>
     <?php
     }
     ?>
  </table>
</div>

5. 創建和下載 CSV 文件

創建download.php文件以創建 CSV 並下載它。

讀取 POSTfrom_date並將to_date其分配給變量。

employee創建一個 SELECT 查詢以從表中獲取記錄。默認情況下選擇所有記錄 iffrom_dateto_date are not set.

如果兩者都設置,則在 WHERE 子句中使用betweenondate_of_joining來選擇記錄。

以寫入模式打開文件並在其中寫入標題列。

循環獲取的記錄並$employee_arr使用所需的值初始化 Array 並寫入文件。

成功創建文件後準備下載並使用 unlink() 方法下載後將其刪除。

完成的代碼

<?php
include "config.php";
$filename = 'employee_'.time().'.csv';

// POST values
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];

// Select query
$query = "SELECT * FROM employee ORDER BY id asc";

if(isset($_POST['from_date']) && isset($_POST['to_date'])){
   $query = "SELECT * FROM employee where date_of_joining between '".$from_date."' and '".$to_date."' ORDER BY id asc";
}

$result = mysqli_query($con,$query);
$employee_arr = array();

// file creation
$file = fopen($filename,"w");

// Header row - Remove this code if you don't want a header row in the export file.
$employee_arr = array("id","Employee Name","Salary","Gender","City","Email","Date of Joining"); 

while($row = mysqli_fetch_assoc($result)){
   $id = $row['id'];
   $emp_name = $row['emp_name'];
   $salary = $row['salary'];
   $gender = $row['gender'];
   $city = $row['city'];
   $email = $row['email'];
   $date_of_joining = $row['date_of_joining'];

   // Write to file 
   $employee_arr = array($id,$emp_name,$salary,$gender,$city,$email,$date_of_joining);
   fputcsv($file,$employee_arr); 
}

fclose($file);

// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/csv; ");

readfile($filename);

// deleting file
unlink($filename);
exit();

6. jQuery

#from_date在和上初始化 jQuery UI 日期選擇器#to_date。將日期格式設置為"yy-mm-dd"並啟用年份更改。

onSelect使用選項添加對日期選擇的驗證。

如果選擇了日期,則通過設置類似的選項不允許日期小於日期,如果選擇了from日期tofrom則通過設置選項不允許日期大於日期。minDate#to_datetofromtomaxDate#from_date

完成的代碼

$(document).ready(function(){

   // From datepicker
   $("#from_date").datepicker({ 
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() + 1);
         $("#to_date").datepicker("option", "minDate", dt);
      }
   });

   // To datepicker
   $("#to_date").datepicker({
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() - 1);
         $("#from_date").datepicker("option", "maxDate", dt);
      }
   });
});

7. 演示

查看演示


8. 結論

POST 日期搜索值並用於betweenSELECT 記錄。使用fputcsv()函數將數據寫入文件。

來源:  https ://makitweb.com

#php #mysql #csv 

如何使用 PHP 按日期範圍以 CSV 格式導出 MySQL 數據
Shayna  Lowe

Shayna Lowe

1659267780

Exporter Des Données MySQL Au Format CSV Par Plage De Dates Avec PHP

Le fichier CSV est utilisé pour l'importation, l'exportation de données et la génération d'un rapport.

Si vous avez d'énormes données disponibles dans la base de données MySQL et que vous n'avez besoin que d'une plage de données spécifique, mais que le fichier contient tous les enregistrements et que vous en avez besoin par vous-même.

En ajoutant un filtre de date au formulaire, il vous suffit de sélectionner la plage et de l'exporter.

Dans ce tutoriel, je montre comment vous pouvez exporter des données de base de données MySQL au format CSV par plage de dates avec PHP. J'utilise jQuery UI pour datepicker.

1. Structure du tableau

J'utilise employeetable dans l'exemple.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL,
  `salary` varchar(30) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(70) NOT NULL,
  `date_of_joining` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Créez une nouvelle config.php connexion à la base de données pour définir.

Code terminé

<?php
$host = "localhost"; /* Host name */$user = "root"; /* User */$password = ""; /* Password */$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

3. Téléchargez et incluez

<!-- jQuery UI CSS -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">

<!-- jQuery --> 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- jQuery UI JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

4.HTML et PHP

Créez un <form method='post' action='download.php' >. Ajoutez deux éléments de texte et un bouton d'envoi.

L'élément de texte est utilisé pour le sélecteur de date.

J'ai créé <table >pour lister tous les enregistrements de la employeetable.

Code terminé

<div >

   <form method='post' action='download.php'>

     <!-- Datepicker -->
     <input type='text' class='datepicker' placeholder="From date" name="from_date" id='from_date' readonly>
     <input type='text' class='datepicker' placeholder="To date" name="to_date" id='to_date' readonly>

     <!-- Export button -->
     <input type='submit' value='Export' name='Export'>
   </form> 
   <table border='1' style='border-collapse:collapse;'>
     <tr>
       <th>ID</th>
       <th>Employee Name</th>
       <th>Salary</th>
       <th>Gender</th>
       <th>City</th>
       <th>Email</th>
       <th>Date of joining</th>
     </tr>
     <?php 
     $query = "SELECT * FROM employee ORDER BY id asc";
     $result = mysqli_query($con,$query);
     
     while($row = mysqli_fetch_assoc($result)){
       $id = $row['id'];
       $emp_name = $row['emp_name'];
       $salary = $row['salary'];
       $gender = $row['gender'];
       $city = $row['city'];
       $email = $row['email'];
       $date_of_joining = $row['date_of_joining'];

     ?>
     <tr>
        <td><?= $id; ?></td>
        <td><?= $emp_name; ?></td>
        <td><?= $salary; ?></td>
        <td><?= $gender; ?></td>
        <td><?= $city; ?></td>
        <td><?= $email; ?></td>
        <td><?= $date_of_joining; ?></td>
     </tr>
     <?php
     }
     ?>
  </table>
</div>

5. Créer et télécharger le fichier CSV

Créer download.phpun fichier pour créer un CSV et le télécharger.

Lisez POST from_dateet to_dateaffectez-le aux variables.

Créez une requête SELECT pour récupérer les enregistrements de la employeetable. Par défaut, sélectionnez tous les enregistrements si from_dateetto_date are not set.

Si les deux sont définis, utilisez betweenon date_of_joiningdans la clause WHERE pour sélectionner des enregistrements.

Ouvrez le fichier en mode écriture et écrivez-y les colonnes d'en-tête.

Bouclez sur les enregistrements récupérés et initialisez $employee_arrArray avec les valeurs requises et écrivez dans le fichier.

Une fois le fichier créé avec succès, préparez-le pour le téléchargement et supprimez-le après le téléchargement en utilisant la  unlink() méthode.

Code terminé

<?php
include "config.php";
$filename = 'employee_'.time().'.csv';

// POST values
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];

// Select query
$query = "SELECT * FROM employee ORDER BY id asc";

if(isset($_POST['from_date']) && isset($_POST['to_date'])){
   $query = "SELECT * FROM employee where date_of_joining between '".$from_date."' and '".$to_date."' ORDER BY id asc";
}

$result = mysqli_query($con,$query);
$employee_arr = array();

// file creation
$file = fopen($filename,"w");

// Header row - Remove this code if you don't want a header row in the export file.
$employee_arr = array("id","Employee Name","Salary","Gender","City","Email","Date of Joining"); 

while($row = mysqli_fetch_assoc($result)){
   $id = $row['id'];
   $emp_name = $row['emp_name'];
   $salary = $row['salary'];
   $gender = $row['gender'];
   $city = $row['city'];
   $email = $row['email'];
   $date_of_joining = $row['date_of_joining'];

   // Write to file 
   $employee_arr = array($id,$emp_name,$salary,$gender,$city,$email,$date_of_joining);
   fputcsv($file,$employee_arr); 
}

fclose($file);

// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/csv; ");

readfile($filename);

// deleting file
unlink($filename);
exit();

6. jQuery

Initialisez jQuery UI datepicker sur #from_dateet #to_date. Définissez le format de date sur "yy-mm-dd"et activez le changement d'année.

Ajouter une validation sur la sélection de la date à l'aide de l' onSelectoption.

Si fromla date est sélectionnée, ne pas autoriser la todate doit être inférieure à la fromdate en définissant l' minDateoption de la #to_datemême manière, si tola date est sélectionnée, ne pas autoriser la fromdate à être supérieure à la todate en définissant l' maxDateoption de #from_date.

Code terminé

$(document).ready(function(){

   // From datepicker
   $("#from_date").datepicker({ 
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() + 1);
         $("#to_date").datepicker("option", "minDate", dt);
      }
   });

   // To datepicker
   $("#to_date").datepicker({
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() - 1);
         $("#from_date").datepicker("option", "maxDate", dt);
      }
   });
});

7. Démo

Voir la démo


8.Conclusion

POSTez les valeurs de recherche de date et utilisez-les betweenpour SÉLECTIONNER les enregistrements. Utilisez fputcsv()la fonction pour écrire des données dans le fichier.

Source :  https://makitweb.com

#php #mysql #csv 

Exporter Des Données MySQL Au Format CSV Par Plage De Dates Avec PHP

Cómo Exportar Datos MySQL En Formato CSV Por Rango De Fechas Con PHP

El archivo CSV se ha utilizado para importar y exportar datos y generar un informe.

Si tiene una gran cantidad de datos disponibles en la base de datos MySQL y solo necesita un rango de datos específico, pero el archivo contiene todos los registros y lo necesita por su cuenta.

Al agregar un filtro de fecha al formulario, solo necesita elegir el rango y exportarlo.

En este tutorial, muestro cómo puede exportar datos de bases de datos MySQL en formato CSV por rango de fechas con PHP. Estoy usando jQuery UI para datepicker.

1. Estructura de la mesa

Estoy usando employeela tabla en el ejemplo.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL,
  `salary` varchar(30) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(70) NOT NULL,
  `date_of_joining` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuración

Cree una nueva config.php para definir la conexión de la base de datos.

Código completado

<?php
$host = "localhost"; /* Host name */$user = "root"; /* User */$password = ""; /* Password */$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

3. Descargar e incluir

  • Descargue las bibliotecas jQuery y jQuery UI .
  • Incluya jquery-ui.css, la biblioteca jQuery y el script jquery-ui.min.js.
<!-- jQuery UI CSS -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">

<!-- jQuery --> 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- jQuery UI JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

4.HTML y PHP

Crea un <form method='post' action='download.php' >. Agregue dos elementos de texto y un botón de envío.

El elemento de texto se utiliza para el selector de fecha.

Creé <table >para enumerar todos los registros de la employeetabla.

Código completado

<div >

   <form method='post' action='download.php'>

     <!-- Datepicker -->
     <input type='text' class='datepicker' placeholder="From date" name="from_date" id='from_date' readonly>
     <input type='text' class='datepicker' placeholder="To date" name="to_date" id='to_date' readonly>

     <!-- Export button -->
     <input type='submit' value='Export' name='Export'>
   </form> 
   <table border='1' style='border-collapse:collapse;'>
     <tr>
       <th>ID</th>
       <th>Employee Name</th>
       <th>Salary</th>
       <th>Gender</th>
       <th>City</th>
       <th>Email</th>
       <th>Date of joining</th>
     </tr>
     <?php 
     $query = "SELECT * FROM employee ORDER BY id asc";
     $result = mysqli_query($con,$query);
     
     while($row = mysqli_fetch_assoc($result)){
       $id = $row['id'];
       $emp_name = $row['emp_name'];
       $salary = $row['salary'];
       $gender = $row['gender'];
       $city = $row['city'];
       $email = $row['email'];
       $date_of_joining = $row['date_of_joining'];

     ?>
     <tr>
        <td><?= $id; ?></td>
        <td><?= $emp_name; ?></td>
        <td><?= $salary; ?></td>
        <td><?= $gender; ?></td>
        <td><?= $city; ?></td>
        <td><?= $email; ?></td>
        <td><?= $date_of_joining; ?></td>
     </tr>
     <?php
     }
     ?>
  </table>
</div>

5. Crear y descargar archivo CSV

Crear download.phparchivo para crear CSV y descargarlo.

Lea POST from_datey to_dateasígnelo a las variables.

Cree una consulta SELECT para obtener registros de employeela tabla. De forma predeterminada, seleccione todos los registros si from_dateyto_date are not set.

Si ambos están configurados, utilice betweenla date_of_joiningcláusula WHERE para seleccionar registros.

Abra el archivo en modo de escritura y escriba columnas de encabezado en él.

Haga un bucle en los registros obtenidos e inicialice $employee_arrArray con los valores requeridos y escriba en el archivo.

Después de la creación exitosa del archivo, prepárelo para descargarlo y elimínelo después de descargarlo usando el  unlink() método.

Código completado

<?php
include "config.php";
$filename = 'employee_'.time().'.csv';

// POST values
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];

// Select query
$query = "SELECT * FROM employee ORDER BY id asc";

if(isset($_POST['from_date']) && isset($_POST['to_date'])){
   $query = "SELECT * FROM employee where date_of_joining between '".$from_date."' and '".$to_date."' ORDER BY id asc";
}

$result = mysqli_query($con,$query);
$employee_arr = array();

// file creation
$file = fopen($filename,"w");

// Header row - Remove this code if you don't want a header row in the export file.
$employee_arr = array("id","Employee Name","Salary","Gender","City","Email","Date of Joining"); 

while($row = mysqli_fetch_assoc($result)){
   $id = $row['id'];
   $emp_name = $row['emp_name'];
   $salary = $row['salary'];
   $gender = $row['gender'];
   $city = $row['city'];
   $email = $row['email'];
   $date_of_joining = $row['date_of_joining'];

   // Write to file 
   $employee_arr = array($id,$emp_name,$salary,$gender,$city,$email,$date_of_joining);
   fputcsv($file,$employee_arr); 
}

fclose($file);

// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/csv; ");

readfile($filename);

// deleting file
unlink($filename);
exit();

6. jQuery

Inicialice el selector de fechas de jQuery UI en #from_datey #to_date. Establezca el formato de fecha "yy-mm-dd"y habilite el cambio de año.

Agregar validación en la selección de fecha usando la onSelectopción.

Si fromse selecciona la fecha, entonces no permitir que tola fecha sea menor que fromla fecha configurando la minDateopción de #to_datemanera similar, si tose selecciona la fecha, entonces no permita que fromla fecha sea mayor que tola fecha configurando la maxDateopción de #from_date.

Código completado

$(document).ready(function(){

   // From datepicker
   $("#from_date").datepicker({ 
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() + 1);
         $("#to_date").datepicker("option", "minDate", dt);
      }
   });

   // To datepicker
   $("#to_date").datepicker({
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() - 1);
         $("#from_date").datepicker("option", "maxDate", dt);
      }
   });
});

7. demostración

Ver demostración


8. Conclusión

PUBLIQUE los valores de búsqueda de fecha y utilícelos betweenpara SELECCIONAR registros. Utilice fputcsv()la función para escribir datos en el archivo.

Fuente:  https://makitweb.com

#php #mysql #csv 

Cómo Exportar Datos MySQL En Formato CSV Por Rango De Fechas Con PHP

Exportar Dados Do MySQL No Formato CSV Por Intervalo De Datas Com PHP

O arquivo CSV é usado para importação de dados, exportação e geração de um relatório.

Se você tiver muitos dados disponíveis no banco de dados MySQL e precisar apenas de dados de intervalo de data específicos, mas o arquivo contém todos os registros e você precisa deles por conta própria.

Ao adicionar um filtro de data ao formulário, você só precisa escolher o intervalo e exportá-lo.

Neste tutorial, mostro como você pode exportar dados do banco de dados MySQL no formato CSV por intervalo de datas com PHP. Estou usando jQuery UI para datepicker.

1. Estrutura da tabela

Estou usando employeea tabela no exemplo.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL,
  `salary` varchar(30) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(70) NOT NULL,
  `date_of_joining` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuração

Crie um novo config.php para definir a conexão do banco de dados.

Código concluído

<?php
$host = "localhost"; /* Host name */$user = "root"; /* User */$password = ""; /* Password */$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

3. Baixe e inclua

<!-- jQuery UI CSS -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">

<!-- jQuery --> 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- jQuery UI JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

4. HTML e PHP

Crie um <form method='post' action='download.php' >. Adicione dois elementos de texto e um botão de envio.

O elemento de texto é usado para o seletor de data.

Criei <table >para listar todos os registros da employeetabela.

Código concluído

<div >

   <form method='post' action='download.php'>

     <!-- Datepicker -->
     <input type='text' class='datepicker' placeholder="From date" name="from_date" id='from_date' readonly>
     <input type='text' class='datepicker' placeholder="To date" name="to_date" id='to_date' readonly>

     <!-- Export button -->
     <input type='submit' value='Export' name='Export'>
   </form> 
   <table border='1' style='border-collapse:collapse;'>
     <tr>
       <th>ID</th>
       <th>Employee Name</th>
       <th>Salary</th>
       <th>Gender</th>
       <th>City</th>
       <th>Email</th>
       <th>Date of joining</th>
     </tr>
     <?php 
     $query = "SELECT * FROM employee ORDER BY id asc";
     $result = mysqli_query($con,$query);
     
     while($row = mysqli_fetch_assoc($result)){
       $id = $row['id'];
       $emp_name = $row['emp_name'];
       $salary = $row['salary'];
       $gender = $row['gender'];
       $city = $row['city'];
       $email = $row['email'];
       $date_of_joining = $row['date_of_joining'];

     ?>
     <tr>
        <td><?= $id; ?></td>
        <td><?= $emp_name; ?></td>
        <td><?= $salary; ?></td>
        <td><?= $gender; ?></td>
        <td><?= $city; ?></td>
        <td><?= $email; ?></td>
        <td><?= $date_of_joining; ?></td>
     </tr>
     <?php
     }
     ?>
  </table>
</div>

5. Crie e baixe o arquivo CSV

Crie download.phpo arquivo para criar o CSV e faça o download.

Leia POST from_datee to_datee atribua-o às variáveis.

Crie uma consulta SELECT para buscar registros da employeetabela. Por padrão, selecione todos os registros se from_dateeto_date are not set.

Se ambos estiverem definidos, use betweenna date_of_joiningcláusula WHERE para selecionar registros.

Abra o arquivo no modo de gravação e escreva as colunas de cabeçalho nele.

Faça um loop nos registros buscados e inicialize $employee_arrArray com os valores necessários e escreva no arquivo.

Após a criação bem-sucedida do arquivo, prepare-o para download e exclua-o após o download usando o  unlink() método.

Código concluído

<?php
include "config.php";
$filename = 'employee_'.time().'.csv';

// POST values
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];

// Select query
$query = "SELECT * FROM employee ORDER BY id asc";

if(isset($_POST['from_date']) && isset($_POST['to_date'])){
   $query = "SELECT * FROM employee where date_of_joining between '".$from_date."' and '".$to_date."' ORDER BY id asc";
}

$result = mysqli_query($con,$query);
$employee_arr = array();

// file creation
$file = fopen($filename,"w");

// Header row - Remove this code if you don't want a header row in the export file.
$employee_arr = array("id","Employee Name","Salary","Gender","City","Email","Date of Joining"); 

while($row = mysqli_fetch_assoc($result)){
   $id = $row['id'];
   $emp_name = $row['emp_name'];
   $salary = $row['salary'];
   $gender = $row['gender'];
   $city = $row['city'];
   $email = $row['email'];
   $date_of_joining = $row['date_of_joining'];

   // Write to file 
   $employee_arr = array($id,$emp_name,$salary,$gender,$city,$email,$date_of_joining);
   fputcsv($file,$employee_arr); 
}

fclose($file);

// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/csv; ");

readfile($filename);

// deleting file
unlink($filename);
exit();

6. jQuery

Inicialize o datepicker da interface do usuário do jQuery em #from_datee #to_date. Defina o formato de data para "yy-mm-dd"e ative a mudança de ano.

Adicione validação na seleção de data usando a onSelectopção.

Se a fromdata for selecionada, não permitir que toa data seja menor que a fromdata, definindo a minDateopção de #to_datesimilarmente, se a todata for selecionada, não permita que froma data seja maior que a todata, definindo a maxDateopção de #from_date.

Código concluído

$(document).ready(function(){

   // From datepicker
   $("#from_date").datepicker({ 
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() + 1);
         $("#to_date").datepicker("option", "minDate", dt);
      }
   });

   // To datepicker
   $("#to_date").datepicker({
      dateFormat: 'yy-mm-dd',
      changeYear: true,
      onSelect: function (selected) {
         var dt = new Date(selected);
         dt.setDate(dt.getDate() - 1);
         $("#from_date").datepicker("option", "maxDate", dt);
      }
   });
});

7. Demonstração

Ver demonstração


8. Conclusão

POST os valores de pesquisa de data e use betweenpara SELECIONAR registros. Use fputcsv()a função para escrever dados no arquivo.

Fonte:  https://makitweb.com

#php #mysql #csv 

Exportar Dados Do MySQL No Formato CSV Por Intervalo De Datas Com PHP
Emmy  Monahan

Emmy Monahan

1658987880

How to Import CSV File Data To MySQL Database in The CodeIgniter 4

In this tutorial, I show how you can import CSV file data in the MySQL database in the CodeIgniter 4 project.

Source: https://makitweb.com

#codeigniter #mysql #csv 

How to Import CSV File Data To MySQL Database in The CodeIgniter 4
Thai  Son

Thai Son

1658977080

Cách Nhập Dữ Liệu Tệp CSV Vào Cơ Sở Dữ Liệu MySQL Trong CodeIgniter 4

CSV là một định dạng được sử dụng rộng rãi để trao đổi dữ liệu giữa các ứng dụng.

Cơ sở dữ liệu và kết xuất dữ liệu phải có định dạng phù hợp để nhập dữ liệu suôn sẻ và không gặp bất kỳ sự cố nào.

Trong hướng dẫn này, tôi chỉ cách bạn có thể nhập dữ liệu tệp CSV trong cơ sở dữ liệu MySQL trong dự án CodeIgniter 4.

1. Cấu hình cơ sở dữ liệu

  • Mở .envtệp có sẵn ở gốc dự án.

LƯU Ý - Nếu dấu chấm (.) Không được thêm vào đầu thì đổi tên tệp thành .env.

  • Xóa # khỏi đầu database.default.hostname, database.default.database, database.default.username, database.default.password và database.default.DBDriver.
  • Cập nhật cấu hình và lưu nó.
database.default.hostname = 127.0.0.1
database.default.database = testdb
database.default.username = root
database.default.password = 
database.default.DBDriver = MySQLi

2. Tạo bảng

  • Tạo một bảng mới usersbằng cách sử dụng di chuyển.
php spark migrate:create create_users_table
  • Bây giờ, điều hướng đến app/Database/Migrations/thư mục từ thư mục gốc của dự án.
  • Tìm một tệp PHP có kết thúc bằng create_users_tablevà mở nó.
  • Xác định cấu trúc bảng trong up()phương thức.
  • Sử dụng down()phương thức xóa usersbảng gọi khi hoàn tác di chuyển.
<?php namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateUsersTable extends Migration
{
    public function up() {
       $this->forge->addField([
          'id' => [
              'type' => 'INT',
              'constraint' => 5,
              'unsigned' => true,
              'auto_increment' => true,
          ],
          'name' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'email' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'city' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'status' => [
              'type' => 'INT',
              'constraint' => '2',
          ],
       ]);
       $this->forge->addKey('id', true);
       $this->forge->createTable('users');
    }

    //--------------------------------------------------------------------

    public function down() {
       $this->forge->dropTable('users');
    }
}
  • Chạy quá trình di chuyển -

php spark di chuyển


3. Cấu trúc tệp CSV

Trong ví dụ, tôi đang sử dụng cấu trúc sau:

Name, Email, City, Status
Yogesh singh, yogesh@makitweb.com, Bhopal, 1
Sonarika Bhadoria, bsonarika@makitweb.com, Delhi, 1
Ankit singh, ankitsingh@makitweb.com, Bhopal, 1

Hàng đầu tiên chứa tên trường.

LƯU Ý - Tôi đang bỏ qua hàng đầu tiên trong khi chèn bản ghi.

Trong khi nhập tệp nếu một hàng không chứa tất cả 4 trường thì tôi sẽ bỏ qua nó.

Nếu trong tệp CSV của bạn, hàng đầu tiên cũng chứa dữ liệu thì bạn chỉ cần xóa một điều kiện trong bộ điều khiển.


4. Mô hình

  • Tạo Users.phptệp trong app/Models/thư mục.
  • Mở tập tin.
  • Chỉ định tên bảng "users"trong $tablebiến, khóa chính "id"trong $primaryKey, kiểu trả "array"về $returnType.
  • Trong $allowedFieldsMảng, chỉ định tên trường - ['name', 'email','city','status']có thể được đặt trong quá trình chèn và cập nhật.

Mã đã hoàn thành

<?php 
namespace App\Models;

use CodeIgniter\Model;

class Users extends Model
{
    protected $table = 'users'; 
    protected $primaryKey = 'id';

    protected $returnType = 'array';

    protected $allowedFields = ['name', 'email','city','status'];
    protected $useTimestamps = false;

    protected $validationRules = [];
    protected $validationMessages = [];
    protected $skipValidation = false;

}

5. Lộ trình

  • Mở app/Config/Routes.phptệp.
  • Xác định 2 tuyến đường -
    • / - Hiển thị chế độ xem tải lên tệp và danh sách người dùng.
    • users / importFile - Nó được sử dụng để tải lên tệp CSV và chèn các bản ghi.

Mã đã hoàn thành

$routes->get('/', 'UsersController::index');
$routes->post('users/importFile', 'UsersController::importFile');

6. Bộ điều khiển

  • Tạo UsersController.phptệp trong app/Controllers/thư mục.
  • Mở tập tin.
  • Nhập Usersmô hình.
  • Tạo hai phương pháp -
    • index () - Chọn tất cả các bản ghi từ  usersbảng và gán cho $data['users']. Tải  users/index chế độ xem và vượt qua  $data.
    • importFile () - Phương thức này được gọi trên biểu mẫu gửi để tải tệp lên và nhập dữ liệu.

Tải lên tệp

Đặt xác thực tệp -

'file' => 'uploaded[file]|max_size[file,1024]|ext_in[file,csv],'
  1. đã tải lên -  Không thành công nếu tên của tham số không khớp với tên của bất kỳ tệp đã tải lên nào.
  2. max_size - Đặt kích thước tải lên tệp tối đa bằng KB -1024 (1 MB).
  3. ext_in - Phần mở rộng tệp hợp lệ - csv.

Nếu tệp chưa được xác thực thì hãy quay lại  users/index chế độ xem với phản hồi xác thực.

Nếu tệp hợp lệ thì hãy tải tệp lên public/csvfilevị trí.

LƯU Ý - csvfile thư mục sẽ được tạo nếu thư mục không tồn tại trong publicthư mục khi tải tệp lên.

Đọc dữ liệu tệp

Mở tệp CSV đã tải lên ở chế độ đọc.

Tôi đã chỉ định tổng số cột trong một hàng - 4 cho $numberOfFields. Sửa đổi giá trị của nó theo số cột trong tệp CSV của bạn.

Vòng lặp trên tệp và đếm tổng số phần tử trong $filedataMảng và gán nó cho $num.

Để bỏ qua hàng đầu tiên của tệp CSV, tôi đã thêm $i > 0điều kiện. Loại bỏ điều kiện này nếu tệp CSV của bạn cũng chứa dữ liệu trên hàng đầu tiên.

Nếu $num == $numberOfFieldssau đó khởi tạo $importData_arrArray. Đặt tên khóa bằng tên trường cơ sở dữ liệu MySQL.

Chèn dữ liệu

Vòng lặp trên $importData_arrMảng và kiểm tra xem email đã tồn tại trong usersbảng hay chưa. Nếu không tồn tại, hãy chèn một bản ghi mới và tăng thêm $count1.

Trả về tổng số bản ghi được chèn bằng flash SESSION.

Mã đã hoàn thành

<?php namespace App\Controllers;

use App\Models\Users;

class UsersController extends BaseController{

   public function index(){
      ## Fetch all records
      $users = new Users();
      $data['users'] = $users->findAll();

      return view('users/index',$data);
   }

   // File upload and Insert records
   public function importFile(){

      // Validation
      $input = $this->validate([
         'file' => 'uploaded[file]|max_size[file,1024]|ext_in[file,csv],'
      ]);

      if (!$input) { // Not valid
         $data['validation'] = $this->validator;

         return view('users/index',$data); 
      }else{ // Valid

         if($file = $this->request->getFile('file')) {
            if ($file->isValid() && ! $file->hasMoved()) {

               // Get random file name
               $newName = $file->getRandomName();

               // Store file in public/csvfile/ folder
               $file->move('../public/csvfile', $newName);

               // Reading file
               $file = fopen("../public/csvfile/".$newName,"r");
               $i = 0;
               $numberOfFields = 4; // Total number of fields

               $importData_arr = array();

               // Initialize $importData_arr Array
               while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
                  $num = count($filedata);

                  // Skip first row & check number of fields
                  if($i > 0 && $num == $numberOfFields){ 
                     
                     // Key names are the insert table field names - name, email, city, and status
                     $importData_arr[$i]['name'] = $filedata[0];
                     $importData_arr[$i]['email'] = $filedata[1];
                     $importData_arr[$i]['city'] = $filedata[2];
                     $importData_arr[$i]['status'] = $filedata[3];

                  }

                  $i++;

               }
               fclose($file);
 
               // Insert data
               $count = 0;
               foreach($importData_arr as $userdata){
                  $users = new Users();

                  // Check record
                  $checkrecord = $users->where('email',$userdata['email'])->countAllResults();

                  if($checkrecord == 0){

                     ## Insert Record
                     if($users->insert($userdata)){
                         $count++;
                     }
                  }

               }

               // Set Session
               session()->setFlashdata('message', $count.' Record inserted successfully!');
               session()->setFlashdata('alert-class', 'alert-success');

            }else{
               // Set Session
               session()->setFlashdata('message', 'File not imported.');
               session()->setFlashdata('alert-class', 'alert-danger');
            }
         }else{
            // Set Session
            session()->setFlashdata('message', 'File not imported.');
            session()->setFlashdata('alert-class', 'alert-danger');
         }

      }

      return redirect()->route('/'); 
   }
}

7. Xem

Tạo một  users  thư mục tại  app/Views/ và tạo index.phptrong usersthư mục.

Hiển thị thông báo cảnh báo bootstrap nếu  'message' tồn tại SESSION. Ngoài ra, hãy đặt lớp cảnh báo bằng  'alert-class' Session.

Tải dịch vụ xác nhận  \Config\Services::validation() và gán nó cho  $validation.

Tạo  <form method="post" action="<?=site_url('users/importFile')?>" enctype="multipart/form-data">.

Tạo một phần tử tệp và nút gửi. Hiển thị lỗi trong  <div > nếu không được xác thực.

Lặp $userslại để hiển thị danh sách người dùng trong <table >.

Mã đã hoàn thành

<!DOCTYPE html>
<html>
<head>
   <title>How to Import CSV file data to MySQL in CodeIgniter 4</title>

   <link rel="stylesheet" type="text/css" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>

   <div class="container">

     <div class="row">
       <div class="col-md-12">
          <?php 
          // Display Response
          if(session()->has('message')){
          ?>
             <div class="alert <?= session()->getFlashdata('alert-class') ?>">
                <?= session()->getFlashdata('message') ?>
             </div>
          <?php
          }
          ?>

          <?php $validation = \Config\Services::validation(); ?>

          <form method="post" action="<?=site_url('users/importFile')?>" enctype="multipart/form-data">

             <?= csrf_field(); ?>
             <div class="form-group">
                <label for="file">File:</label>

                <input type="file" class="form-control" id="file" name="file" />
                <!-- Error -->
                <?php if( $validation->getError('file') ) {?>
                <div class='alert alert-danger mt-2'>
                   <?= $validation->getError('file'); ?>
                </div>
                <?php }?>

             </div>

             <input type="submit" class="btn btn-success" name="submit" value="Import CSV">
          </form>
       </div>
     </div>

     <div class="row">

        <!-- Users list -->
        <div class="col-md-12 mt-4" >

           <h3 class="mb-4">Users List</h3>
           <table width="100%">
              <thead>
                 <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>City</th>
                    <th>Status</th>
                 </tr>
              </thead>
              <tbody>
                 <?php 
                 if(isset($users) && count($users) > 0){
                     foreach($users as $user){
                 ?>
                       <tr>
                          <td><?= $user['id'] ?></td>
                          <td><?= $user['name'] ?></td>
                          <td><?= $user['email'] ?></td>
                          <td><?= $user['city'] ?></td>
                          <td><?= $user['status'] ?></td>
                       </tr>
                 <?php
                     }
                 }else{
                 ?>
                     <tr>
                        <td colspan="5">No record found.</td>
                     </tr>
                 <?php
                 }
                 ?>
             </body>
           </table>
        </div>

     </div>
   </div>

</body>
</html>

8. Chạy

  • Điều hướng đến dự án bằng Command Prompt nếu bạn đang sử dụng Windows hoặc terminal nếu bạn đang sử dụng Mac hoặc Linux, và
  • Thực hiện lệnh “php spark serve”.
php spark serve
  • Chạy  http://localhost:8080 trong trình duyệt web.

9. Kết luận

Trong ví dụ này, tôi đọc từng hàng tệp CSV và kiểm tra xem tất cả các trường đều có sẵn hay không. Nếu có sẵn thì tôi đã chèn nó.

Thêm xác thực bắt buộc để tránh trùng lặp dữ liệu và trước khi chèn cũng kiểm tra xem các giá trị có ở định dạng được yêu cầu hay không.

Nguồn:  https://makitweb.com

#codeigniter #mysql #csv 

Cách Nhập Dữ Liệu Tệp CSV Vào Cơ Sở Dữ Liệu MySQL Trong CodeIgniter 4